# Extract numbers from text and number cell

#### Excellent865

##### New Member
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!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Rick Rothstein

##### MrExcel MVP
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.

Replies
3
Views
213
Replies
13
Views
659
Replies
14
Views
267
Replies
10
Views
866
Replies
15
Views
307

1,171,463
Messages
5,875,662
Members
433,146
Latest member
MinhTrang

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back