# Extracting Number only from string of text

#### nirvehex

##### Active Member
Hi,

I have a formula:
Code:
=IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3)

This returns SPEC-8 or BID-10 or SPEC-4 or BID-1.5, ETC

All I want to do is put a function around the outside of this formula that extracts only the number.

So it would read 8 if the result of the inside formula was SPEC-8 or 1.5 if it was BID-1.5.

I'm wondering if there's a custom VBA function to do it that would look like this:

Code:
=ExtractNumber(IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3))

Any ideas?

Thanks!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Rick Rothstein

##### MrExcel MVP
See if this formula gives you want you want (I have already incorporated the formula you posted into it)...

=TRIM(RIGHT(SUBSTITUTE(IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3),"-",REPT(" ",99)),99))

#### Vidar

##### Well-known Member
Try this:
=MID(IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3),SEARCH("-",IFERROR(IF(BJ3>Y3,M3,VLOOKUP(F3,'Bid Template'!L:CU,88,FALSE)),M3))+1,99)+0
The +0 at the end convert the value from text to number if you need o do calculations With it afterwards.
Skip +0 if you want the result to be formatted as text.

#### nirvehex

##### Active Member
Both formulas almost worked, except sometimes I have "BID-.48" and they both turned this number into 19389.51. I just want it to turn into .48. For all the whole numbers it worked fine though. Any ideas?

Last edited:

#### Rick Rothstein

##### MrExcel MVP
Both formulas almost worked, except sometimes I have "BID-.48" and they both turned this number into 19389.51. I just want it to turn into .48. For all the whole numbers it worked fine though. Any ideas?
You apparently did not see the formula I posted in Message #2 yet as I do not think it could be returning such a value.

By the way, it is always a good idea to post who you are replying to when you have more than one response.

#### nirvehex

##### Active Member
You apparently did not see the formula I posted in Message #2 yet as I do not think it could be returning such a value.

By the way, it is always a good idea to post who you are replying to when you have more than one response.

I did see your message. However, it was my fault, I had a vlookup in pulling off of another tab. My apologies

#### Rick Rothstein

##### MrExcel MVP
I did see your message. However, it was my fault, I had a vlookup in pulling off of another tab. My apologies

So did the formula work for you then?

Replies
0
Views
271
Replies
1
Views
896
Replies
2
Views
702
Replies
7
Views
264
Replies
13
Views
812

1,190,790
Messages
5,982,936
Members
439,808
Latest member
agutosay

### 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