Extract string of numbers

[carlmov]

Hello all,
I want to extract numbers using ONLY formula not text to column as follows:

1,2,12,14,15
3,5,6,10,18,22
8,17,32,45

<tbody>
</tbody>

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
1	1,2,12,14,15	1	2	12	14	15
2	3,5,6,10,18,22	3	5	6	10	18	22
3	8,17,32,45	8	17	32	45

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula B1 =IFERROR(TRIM(LEFT(SUBSTITUTE(MID(","&$A1&",",FIND("|",SUBSTITUTE(","&$A1&",",",","|",COLUMN(B1)-1))+1,LEN($A1)),",",REPT(" ",LEN($A1))),LEN($A1)))+0,"") <tbody> </tbody>

<tbody>
</tbody>

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.