# ISNUMBER returning FALSE with function?

#### Jconafay

##### New Member
Hello all,

I'm currently trying to extract numbers from a column that is formatted as follows:

Blah Blah For Blahing (0284)

And am using this function which generally works:

=MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1))

Which works BEAUTIFULLY, EXCEPT in the case of something like this:

Blahdiblah (BDB) (0247)

Which returns the acronym instead of the number.. I thought I came up with a really elegant solution by putting this out there:

=IF(ISNUMBER(MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1)),MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1), "WHY?!")

But the ISNUMBER function is returning as false even when I isolate the =ISNUMBER(MID.... etc

Any help on this or anything I'm missing that would make it return false like it is? I'm now realizing that I'll have to do some other magic to skip the text even if this does resolve as TRUE, but I'll get to that?

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### bosco_yip

##### Well-known Member
Try,

=IF(ISNUMBER(0+MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1)),MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1), "WHY?!")

Regards

##### MrExcel MVP
Does this work for you?

=SUBSTITUTE(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1),""),")","")

#### FDibbins

##### Well-known Member
Any time you use LEFT, MID or RIGHT, you generate a text answer, even if it looks like a number - so you need to convert the answer to a number...

--MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1))
MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1))*1
MID(I2, SEARCH("(", I2) + 1, SEARCH(")", I2) - SEARCH("(", I2)-1))+0

#### Jconafay

##### New Member
I ended throwing a simple *1 in at the end. Thank you, it worked! Now to tackle the problem of only returning the numbers..

##### MrExcel MVP
I ended throwing a simple *1 in at the end. Thank you, it worked! Now to tackle the problem of only returning the numbers..

My goodness...

=SUBSTITUTE(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1),""),")","")+0

#### Tetra201

##### MrExcel MVP
... I'm currently trying to extract numbers from a column that is formatted as follows:

Blah Blah For Blahing (0284)
If your bracketed numbers are always at the end of the string and always have four digits, see if the following formula works for you:

=--LEFT(RIGHT(I2,5),4)

#### Jconafay

##### New Member
This 100% worked, thank you so much, didn't see it before and thank you everyone for the quick replies! Very much appreciated.

##### MrExcel MVP
This 100% worked, thank you so much, didn't see it before and thank you everyone for the quick replies! Very much appreciated.

You are welcome.

#### Tetra201

##### MrExcel MVP
This 100% worked, thank you so much, didn't see it before and thank you everyone for the quick replies! Very much appreciated.
You are welcome.
Simple solutions do work.

Replies
6
Views
190
Replies
3
Views
690
Replies
1
Views
124
Replies
2
Views
166
Replies
3
Views
323

1,191,584
Messages
5,987,492
Members
440,097
Latest member
Wint

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