Extract numbers from text and number cell

[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 am looking to do this without using VBA

I would really appreciate any help!

 

[Rick Rothstein]

If all of your text only have three numbers in them to be extracted, then you can use this formula...

=0+TRIM(MID(SUBSTITUTE(A$1,"""",REPT(" ",99)),(2*ROWS($2:2)-1)*99,99))

If, on the other hand, the number of numbers to be extracted can vaiy and be more than thee, uses this formula instead...

=IFERROR(0+TRIM(MID(SUBSTITUTE(A$1,"""",REPT(" ",99)),(2*ROWS($2:2)-1)*99,99)),"")

NOTE: Your posted example uses a stylized quote mark... I assumed that is not what is in your actual cell, rather, I assumed your text has normal quote marks in them.