# Returning numbers separated by commas

##### New Member
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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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.

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

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

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.

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.

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.

How do the cells like 3,123,456,321 get there in the first place?

Yep,

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

Cheers

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.

Replies
6
Views
151
Replies
3
Views
128
Replies
4
Views
180
Replies
8
Views
396
Replies
4
Views
383

1,211,454
Messages
6,101,942
Members
447,764
Latest member
gopalgriffith

### 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.

### Which adblocker are you using?

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

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