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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

macajm

New Member
Joined
Oct 21, 2005
Messages
32
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,407
Office Version
  1. 365
Platform
  1. Windows
ToadOnRoll

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

ToadOnRoll

New Member
Joined
Sep 21, 2006
Messages
7
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,407
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

ToadOnRoll

New Member
Joined
Sep 21, 2006
Messages
7
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.
 

macajm

New Member
Joined
Oct 21, 2005
Messages
32

ADVERTISEMENT

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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,407
Office Version
  1. 365
Platform
  1. Windows
How do the cells like 3,123,456,321 get there in the first place?
 

ToadOnRoll

New Member
Joined
Sep 21, 2006
Messages
7
Yep,

That's done it. Not very elegant, but I'll hide it all up!

Thanks for your help.

Cheers
 

Forum statistics

Threads
1,136,267
Messages
5,674,729
Members
419,523
Latest member
Urnovio

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
Top