Text to column formula

[Excellent865]

Hello,

I am looking for a way to extract a number from a string or text, symbols and numbers. For example I have the following in A1

{Low:”100″,medium:”300″,high:”5000″}

I would like to take the low number in A2, the medium number in A3 and the high number in A4.

I have tried a series of mid(Len) formulas but as it counts the number of characters, if my number is a varying number of digits it will only take the first numbers.

I am looking for something similar to text to columns as the symbols will stay the same but as a formula.

I would really appreciate any help!

 

[sheetspread]

Excel 2010
	A
1	{Low:"100",medium:"300",high:"5000"}
2	100
3	300
4	5000
Sheet5

Cell Formulas
Range	Formula
A2	=TRIM(MID(SUBSTITUTE(A$1,"""",REPT(" ",100)),(ROW(A1)*200)-100,100))

 

[Takae]

UDF
A2 =IFERROR(smp($A$1,ROW(A1)),"")

Function smp(ByVal txt As String, ByVal ref As Long) As String
With CreateObject("VBScript.RegExp")
    .Pattern = ("\d+")
    .Global = True
    smp = .Execute(txt)(ref - 1)
End With
End Function

 

[Excellent865]

Hello,

Thank you for the response. When I enter this, I get an empty answer. Do you have any other ideas?

 

[Excellent865]

Hello,

Thank you for the response. I am looking for a solution that does not involve a macro.
Do you have any other recommendations?

 

[sheetspread]

Hello,

Thank you for the response. When I enter this, I get an empty answer. Do you have any other ideas?

You're using angled quotes, be sure those in the formula match