Share via

Splitting numbers in one cell into different cells

Anonymous
2012-03-13T15:23:16+00:00

How can I split numbers in cell that are separated by a comma into different cells?

I need a function to do it, as I want it to happen automatically without having to do it myself

For example

                A                                                  B                                     C                                          D

1      198311, 211311,                       198311                           211311

2      311287,                                        311287

3      116765, 876545, 876567,        116765                          876545                              876567

So I need a function in columns B,C, and D to split the numbers in column A that are seperated by a comma followed by a space

Thanks

John

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2012-03-13T15:55:31+00:00

Thanks. Unfortunately some numbers are 5 digits. However, each number is always preceeded by 1 space and followed immediately by a comma. Does this help in any way?

 

John

Hi,

Select the column of numbers then

Data tab|Text to columns|Select 'Delimited|Next|Select comma|Finish

Was this answer helpful?

2 people found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2012-03-13T15:31:24+00:00

... So I need a function in columns B,C, and D to split the numbers in column A that are seperated by a comma followed by a space...

Typically you would use Text to Columns from the Data tab's Data Tools group but this will put the results into columns A, B and C. If you need the original column intact, copy it to column B, select column abd use Text to Columns, Delimited, Comma, Finish. This operation could be recorded as a macro for future use.

Combinations of LEFT(), MID() and FIND() could also be used, but the complication is the varying number of digit sets that are comma deliminated.

Edit: the following formula will work for ~15 numbers sets in the format that you have shown in your sample, e.g. <six digits>, <six digits>, <six digits>, <six digits>,etc. Just put it into B1 and fill right.

=TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),1+(COLUMNS($A:A)-1)*99,99))

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-03-13T15:56:45+00:00

    ...  Unfortunately some numbers are 5 digits. However, each number is always preceeded by 1 space and followed immediately by a comma.

    Text to Columns will work if you select Delimited on the first page of the wizard and Comma on the second. I fear you left it as Fixed width on the first page.

    I've also belatedly supplied a formula in my first response.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2012-03-13T15:52:00+00:00

    Thanks. Unfortunately some numbers are 5 digits. However, each number is always preceeded by 1 space and followed immediately by a comma. Does this help in any way?

    John

    Was this answer helpful?

    0 comments