How to split comma separated numbers using a formula

I have a long, comma-separated list which looks like this in Excel:

481703472.963,2761810387.418,34630.525
481703472.963,2761810387.418,34630.526
481703472.963,2761810387.418,34630.527

I need a formula to convert this into Six separate columns.

It should look like as per image:

3 Answers

This can be fixed even using built in Text to Column command.

Caveat:

• @Máté Juhász edited the posted, as well uploaded the required output after this solution have been posted.
• My solution addresses original issue as shown with Before split captioned picture.

Before split:

• From Data TAB hit Text To Column.
• Select Delimited Option.
• Move to Next screen.
• Now Check Comma & Other Check box.
• Insert . symbol for Other Text box.
• Finally hit Finish.

You get this after split:

N.B. Just for better look I've Bold faced the Cells T1 & T2, you may ignore this.

Edited:

I'm posting this solution based on Excel Formula, since OP is in need of simple split into columns, confirmed through comments below.

• Formula in Cell V8 & fill it across:

  =TRIM(MID(SUBSTITUTE("."&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($T8,",","."),",","."),",","."),".",REPT(" ",50)),50*COLUMNS($T:T),50))

Adjust cell reference and delimiters in the formula as needed.

If you need just a few columns and number of digits is always the same, then just use MID():

=MID($A2,10,5)

If, as you wrote above, you are only interested in returning the values showing in columns 1,3,5, you can try this formula:

=IFERROR(INDEX(LEFT(TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),seq_99,99)),-1+FIND(".",TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),seq_99,99)))),COLUMNS($A:A)),"")

Where seq_99 is a Named Formula that refers to:

=IF(ROW($A$1:INDEX($A:$A,255,1))=1,1,(ROW($A$1:INDEX($A:$A,255,1))-1)*99)