How do I merge four columns into one?

[jackblack2]

Hi,

Can anyone tell me how to merge all data in column C, column E, column G and column I into column A (see attached spreadsheet)?

Is there a button in the task bar or does one need to paste in formulas?

Many thanks,

Jackblack2

[AliGW]

There is no data in your attachment, so nothing to merge.

You can use the & function:

=C1&E1&G1

or the CONCATENATE function:

=CONCATENATE(C1,E1,G1)

or, if you have it in your version, TEXTJOIN.

Then there's PowerQuery.

Lots of options, but no data, so can't advise any more specifically.

[jackblack2]

Oops, I meant not merge individual cells but in the manner the data appears in the attached updated spreadsheet.

Each of the four columns contain data in rows 1 - 125.

Thanks again,

Jackblack2

[AliGW]

I have no idea what you mean - sorry. Where are these numbers coming from? Your spreadsheet still doesn't show data merged anywhere.

[jackblack2]

Do you not see number 1 - 39 in the attached spreadsheet in my reply?

[AliGW]

What about it? Is that the merged data?

Please bear in mind that the majority of users here have spreadsheets that read left to right - you need to use more signposting in your attachments to show CLEARLY what you want, as most of us are seeing this back-to-front.

[Bo_Ry]

Please try at A1
=SMALL($C$1:$I$19,ROW(A1))

[jackblack2]

@Bo_Ry

Thats the formula I need. But I need it to be extended down to row 500 in column A, since the data in each of the 4 columns extend down to rows 125. I should have explained that in the beginning. Sorry.

@AliGW The problem you described reminds me of the issue I have trying to drive in the U.K on the left coming from Israel

[AliGW]

Lol!!!!!!

[Bo_Ry]

Please try =SMALL($C$1:$I$125,ROW(A1))

[jackblack2]

@Bo_Ry Thanks a fortune. The formula even sorts the data from smallest to largest.

Great forum. You guys need a salary from Microsoft