Bill4tektips
Technical User
I have a spreadsheet with a column containing several numbers separated by a comma. Is there any way to split them out into individual columns. Numbers are in the format 12345,45678,
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Help 1
- Thread starter Bill4tektips
- Start date
- Status
-
1
- #2
Easiest way as a one off solution is to do
Data>Text to Columns and use "," as the delimeter
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
Data>Text to Columns and use "," as the delimeter
Rgds, Geoff
We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.
Please read FAQ222-2244 before you ask a question
mrstaggart
MIS
That is a great solution!
Another I've used is the MID formula based on the extracted number above. Like this:
Data in A2
11111, 22222, 33333, 44444, 55555
=LEFT(A2, 5) you get ---->11111
=MID($A$2, SEARCH(A3, $A$2, 1)+LEN(A4)+2, 5)
you get ---->22222
where A2 is where the bulk of data sits
Search gives you the numeric location of the start of the number found in A3 (the LEFT formula)
plus the length of the number above plus 2 for the comma and space in my example.
The last "5" is how many characters to grab.
Copy this down your column and you have it.
Another I've used is the MID formula based on the extracted number above. Like this:
Data in A2
11111, 22222, 33333, 44444, 55555
=LEFT(A2, 5) you get ---->11111
=MID($A$2, SEARCH(A3, $A$2, 1)+LEN(A4)+2, 5)
you get ---->22222
where A2 is where the bulk of data sits
Search gives you the numeric location of the start of the number found in A3 (the LEFT formula)
plus the length of the number above plus 2 for the comma and space in my example.
The last "5" is how many characters to grab.
Copy this down your column and you have it.
- Status