Returning numbers separated by commas

ToadOnRoll

New Member
Joined
Sep 21, 2006
Messages
7
Hello All,

I have columns which have numbers separated by comma. E.g.

3,135,234
123,23,456,765
23,2345

etc. etc.

Does anyone know how I can return the numbers into separate cells? So one cell for the first number, next cell for 2nd number and so on. Obviously some codes have more than 2 sets of numbers and each set of numbers is made up of a different amount of integers.

I'd rathee use a simple formulae, but is this something a macro could do for me?

Thankyou

ToadOnRoll
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Have you tried using the Excel feature called "Text to columns"?
select data (all in one column) and then Datas Menu| Text to Columns.
then chooes delimited data and check the comma box and OK.
this will split each cell at the comma and put successive numbers into adjacent columns.
 
Upvote 0
ToadOnRoll

Try this:
1. Select the column by clicking its heading label.
2. Data|Text to Columns...|Delimited|Next|Comma|Finish
 
Upvote 0
Thanks for those.

I've rhied this function but it only seems to remove the commas. Hence 3,123,326 becomes 3123326. When I need it to return 3 in one column, 123 in the next and so on...

Am I using this function incorrectly?

Cheers
 
Upvote 0
Thanks for those.

I've rhied this function but it only seems to remove the commas. Hence 3,123,326 becomes 3123326. When I need it to return 3 in one column, 123 in the next and so on...

Am I using this function incorrectly?

Cheers
I think what you have in this cell may not be three numbers separated by commas but the number 3123326 formatted with the comma format. Select the cell and try Format|Cells...|Number|Text|OK and see if the commas just disappear.

Also try the 'text to columns' process on a cell that doesn't have 3 digits between commas.
 
Upvote 0
OK.

I can get it to work for the column of dats which has numbers 14,45 or 45. Etc. But it doesn't seem to like the numbers which appear to be in a standard format (even though they aren't supposed to be). eg 3,123,456,321.

If you have any more ideas I'd be very grateful.
 
Upvote 0
I suspect that the apparent set of comma separated numbers you have is in fact just that - a large number - and not text which we were expecting. The commas only exist in the formatting.
If this is really the case (ie that it is a number and not text) then it is a little more complex.

One (not very elegant) solution would be:-

If your original data is in column A, then in column B enter formulas to convert the numbers to comma separated text :-
B1= text(A1,"###,###,###,###") and then copy down for the extent of your data.
Then copy column B and PasteSpecial|Values in say Column C
Then run the Data|TexttoColumns etc on Column C to parse into columns.
 
Upvote 0
How do the cells like 3,123,456,321 get there in the first place?
 
Upvote 0
ToadOnRoll, did you find an answer to this issue that you can share with me? Would be much obliged if you could share it with me.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top