ISNUMBER returning FALSE with function?

[Jconafay]

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?

Thanks in advance!

 

[bosco_yip]

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

 

[Aladin Akyurek]

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]

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]

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

 

[Aladin Akyurek]

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]

... 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]

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

 

[Aladin Akyurek]

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]

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.