Extract string of numbers

[carlmov]

Hello all,
I want to extract numbers using ONLY formula not text to column as follows:
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]1,2,12,14,15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3,5,6,10,18,22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8,17,32,45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]





I have either 1 or 2 digit numbers not more. I tried Left, mid, and right with find and substitute using columns as the variable (+1 and -) for increments, but didn't get it right altogether.

Kindly, can you give a robust formula.
Thank you and all the best for 2018,
carlo

 

[Max1616]

Hello,

Lets assume your string of numbers lives in column A, starting in cells A1. Paste this formula in B1 and copy it to the range B1:J3:
=IFERROR(IFERROR(IF(FIND(" ",SUBSTITUTE($A1,","," ",COLUMN(B:B)-1))=2,LEFT($A1,1),SUBSTITUTE(MID($A1,FIND(" ",SUBSTITUTE($A1,","," ",COLUMN(B:B)-1))-2,2),",","")),MID($A1,FIND(" ",SUBSTITUTE($A1,","," ",COLUMN(B:B)-2))+1,2)),"")

For this string "1,2,12,14,15" in A1 your result will be as follows:
B1= 1
C1= 2
D1= 12
E1= 14
F1=15

Let me know if this works and if you have any questions.

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1,2,12,14,15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]3,5,6,10,18,22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]22[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8,17,32,45[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]45[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(","&$A1&",",FIND("|",SUBSTITUTE(","&$A1&",",",","|",COLUMN(B1)-1))+1,LEN($A1)),",",REPT(" ",LEN($A1))),LEN($A1)))+0,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


One more way, shorter, but not fully tested. It does work on the sample above though:

B1: =IFERROR(MID(SUBSTITUTE(","&$A1,",",REPT(" ",99)),(COLUMN(B1)-1)*99,99)+0,"")

 

[admiral100]

Another way :

=TRIM(MID(SUBSTITUTE(","&REPLACE($A1,LOOKUP(1,-MID($A1,ROW($A$1:$A$200),1),ROW($A$1:$A$200))+1,,","),",",REPT(" ",200)),200*COLUMNS($A:A),200))

 

[carlmov]

Thank you Max. Much appreciated

 

[carlmov]

Thanks Eric, well done

 

[carlmov]

Thank you Admiral.