Transpose and Concatenate help

[Ainsley]

Hi, I have a long list of numbers in column A (A2:A50) and I need to show these in one single cell. With a space separating each number.

How do I do that ? I've tried to concatenate and transpose in one go and can get them all in one cell, but without a space. I tried to put a column of spaces in B, but still couldn't make it work.

Many thanks.

[igoodable]

=CONCATENATE(A2," ",A3," ",A4 etc....)

[Ainsley]

OK, but is there a quicker way to write that formula. My list may well become hundreds of lines long soon and typing each cell followed by " " will take forever.

Any suggestions ?

[mahju]

In B3 use this formula

Please Login or Register  to view this content.

and then in B4 use this

Please Login or Register  to view this content.

Copied down.

[Ainsley]

Ah, brilliant. Thanks

[mahju]

Hi
Welcome

[AlKey]

You can also do it with TRANSPOSE function

Row\Col	A	B
1	1	Start with TRANSPOSE function and type in formula then select range as shown: Do not press ENTER, isntead press F9 and Enter. Now add CONCATENATE( and delete curly brackets and the last comma and hit ENTER.
2	727	=TRANSPOSE(A2:A10&", ")
3	532	=CONCATENATE({"727, ","532, ","363, ","146, ","517, ","101, ","714, ","149, ","419, "})
4	363
5	146
6	517
7	101
8	714
9	149
10	419

[AlKey]

Deleted duplicate