Transferring a column of numbers to a vertical string
Hi
I'm new here and had a little scoot about but couldn't see a topic relating to my issue.
I have a column of numbers that i need to change to a single row of numbers split with a ";"
EG
Column A
12345
67890
21234
31234
41234
Needs to be:
12345; 67890; 21234; 31234; 41234
I had thought transpose would sort this but it doesn't... it gives me one set of numbers per cell in a row rather than all numbers in one cell.
any help you can give would be awesome!
A simple way is to put this formula in B1:
=A1
then put this formula in B2:
=B1 &"; " & A2
then copy this down as far as you need to - a quick way is to select B2 then double-click the fill handle (the small black square in the bottom right corner of the cursor). Your composite string will be in the bottom cell, but you could have it in C1 (for example) by using this formula there:
=INDEX(B:B,COUNTA(B:B))
Hope this helps.
Pete
Last edited by Pete_UK; 10-24-2012 at 11:05 AM. Reason: cells wrong way round
How about a UDF (Thank you Google!):
If your range of numbers is in column A, then in B1 enter: =concat(A:A)
Please Login or Register to view this content.
Thx for ur help guys! Outstanding! Really quick responses too. I am a happy bunny now!!!
Thx
Shellz x
Here's another UDF.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks