Extracting numbers from text

[Nitesh791102]

Hi All,

I need to extract all the numbers separated by comma from the text in the following format:

Adam-Graes, Pat, ;#450;#Baz, Bad, ;#301;#George, Al, ;#285;#Heney, Matew, M;#421;#Vinky, Eott, ;#339;#Yan, Elizabet, ;#338

Desired output: 450, 301, 285, 421, 339, 338

Thank you in advance.
Nitesh

 

[Hui]

Nitesh

If this is as long as the strings are I'd use this:
=TEXT(NPV(-0.9,,IFERROR(MID(LEFT(A1,LEN(A1)/2),1+LEN(LEFT(A1,LEN(A1)/2))-ROW(OFFSET(A$1,,,LEN(LEFT(A1,LEN(A1)/2)))),1)%,"")),"#,###")&","&TEXT(NPV(-0.9,,IFERROR(MID(MID(A1,LEN(A1)/2+1,999),1+LEN(MID(A1,LEN(A1)/2+1,999))-ROW(OFFSET(A$1,,,LEN(MID(A1,LEN(A1)/2+1,999)))),1)%,"")),"#,###") Ctrl+Shift+Enter



If the strings are longer I'd suggest using a User Defined Function

 

[Nitesh791102]

Great Thanks, Hui.

This is really mindblowing.However, if I apply the same formula to
"Kwiat, Jane, ;#366;#Saya, Farz, ;#343", the output which I get is 36,6,343 (instead of 366, 343).

I have attached the example dataset for your reference.

Could you please help me get out of this problem.

Thanks again.

 

[Hui]

Try using just the first half of the formula:
=TEXT(NPV(-0.9,,IFERROR(MID(LEFT(A1,LEN(A1)/2),1+LEN(LEFT(A1,LEN(A1)/2))-ROW(OFFSET(A$1,,,LEN(LEFT(A1,LEN(A1)/2)))),1)%,"")),"#,###")

 

[Nitesh791102]

Thanks Hui,

The challenge is that I have simiar text strings with varying length in one column from which I want to extract the numbers (Example dataset in previous string). Please let me know your thoughts on this.

Thanks.
Nitesh