Extracting numbers from Excel Cell

[wooly_sohail]

Extracting all numbers from single cell and separating each of them by ":" Ex:- If a cell has $85.5_Michigan $89_Illinois Kentucky_$82 Missouri_$65 Ohio_$194; Tennessee; 256; Kansas; Florida; California; Ottawa; Waterloo; Ontario I would like to have 85.5;89;82;65;194;256 as output in a single excel cell.

 

[Fluff]

For that example, how about

Fluff.xlsm
	A	B
1
2	$85.5_Michigan $89_Illinois Kentucky_$82 Missouri_$65 Ohio_$194; Tennessee; 256; Kansas; Florida; California; Ottawa; Waterloo; Ontario	85.5;89;82;65;194;256
Report

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(";",,FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),CHAR(10)," "),"_"," "),";"," ")," ","</m><m>")&"</m></k>","//m[.=number()]"))

 

[wooly_sohail]

Thanks a ton !!!

 

[Peter_SSs]

I think this should do it for you too & you don't need to specify all the 'extra' characters that you don't want.
It does assume that the only "." characters will be within numbers as per your one example.

22 07 29.xlsm
	A	B
1
2	$85.5_Michigan $89_Illinois Kentucky_$82 Missouri_$65 Ohio_$194; Tennessee; 256; Kansas; Florida; California; Ottawa; Waterloo; Ontario	85.5;89;82;65;194;256
Extract nums

Cell Formulas
Range	Formula
B2	B2	=LET(ch,MID(A2,SEQUENCE(LEN(A2)),1),SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(ch+0)+(ch="."),ch," ")))," ",";"))

 

[Fluff]

Glad we could help & thanks for the feedback.