Excel how to merge 2 columns

Question

Excel how to merge 2 columns

Antonella Ficini 1

If I have the following excel 2 colums
a 1
b 2
c 3
How can I get a single column formatted like this
a
1
b
2
c
3
Unfortunately using autofill skips some cells

Michael Taylor 60,161 Reputation points

2023-01-05T15:46:41.07+00:00

This is the English Q&A forums. Please update your question to be in English. Alternatively you can submit your question to the Q&A forums for your locale.
Antonella Ficini 1 Reputation point

2023-01-05T16:18:03.277+00:00

If I have the following excel 2 colums
a 1
b 2
c 3
How can I get a single column formatted like this

a
1
b
2
c
3
Unfortunately using autofill skips some cells
Michael Taylor 60,161 Reputation points

2023-01-05T16:27:26.693+00:00

Please edit your original question and convert to English. Folks aren't going to jump to comments to see what you were asking. At that point someone may be able to help you. I don't know how to do this in Excel and I'm pretty sure neither Transpose or Autofill can do it. I think you'll have to write a macro but I'll let the experts help you.

3 answers

Your answer

Michael Taylor 60,161 Reputation points

2023-01-05T15:46:41.07+00:00

This is the English Q&A forums. Please update your question to be in English. Alternatively you can submit your question to the Q&A forums for your locale.
Antonella Ficini 1 Reputation point

2023-01-05T16:18:03.277+00:00

If I have the following excel 2 colums
a 1
b 2
c 3
How can I get a single column formatted like this

a
1
b
2
c
3
Unfortunately using autofill skips some cells
Michael Taylor 60,161 Reputation points

2023-01-05T16:27:26.693+00:00

Please edit your original question and convert to English. Folks aren't going to jump to comments to see what you were asking. At that point someone may be able to help you. I don't know how to do this in Excel and I'm pretty sure neither Transpose or Autofill can do it. I think you'll have to write a macro but I'll let the experts help you.

Answer 1

Lz._ 9,016

Alternatively, with Microsoft/Office 365:

Answer 2

Emily Hua-MSFT 27,796

Hi @Antonella Ficini

Welcome to Q&A forum ~

Please check whether the formula is helpful.
=OFFSET(A$1,INT((ROW(A1)-1)/2),MOD(ROW(A1)-1,2))

Please note, the part of INT((ROW(A1)-1)/2) can be INT((ROW(A1)-Row($A$1))/2), and MOD(ROW(A1)-1,2) can be MOD(ROW(A1)-Row($A$1),2). Cell A1 is the first cell in my sample, you can change it as yours first one.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Emily Hua-MSFT 27,796 Reputation points

2023-01-09T07:11:43.407+00:00

Hi @Antonella Ficini
If you have a chance to be back, please check whether my information is helpful.
Ceasar Chen_MSFT 4,481 Reputation points

2023-02-01T01:52:10.09+00:00

Hi, @Antonella Ficini

Did you refer to Emily Hua-MSFT's response, and the problem was solved?

Answer 3

Lz._ 9,016

With Excel 2021

=LET(
  rng, A1:B3,
  cls, COLUMNS(rng),
  sek, SEQUENCE(cls*ROWS(rng),,0),
  INDEX(rng, 1+QUOTIENT(sek,cls), 1+MOD(sek,cls))
)

Share via

Excel how to merge 2 columns

3 answers

Your answer