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!

 

[Rick Rothstein]

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)

If your assumptions are always true, then there is a much simpler formula available...

=-RIGHT(I2,6)

Note that there is only a single minus sign in front of the RIGHT function name.

 

[Tetra201]

If your assumptions are always true, then there is a much simpler formula available...

=-RIGHT(I2,6)

Note that there is only a single minus sign in front of the RIGHT function name.

Neat!

 

[Rick Rothstein]

Neat!

Yeah, I know... it is neat how Excel thinks a number in parentheses is the negative of what is contained within the parentheses.

By the way, if the number in the parentheses is always at the end, but the number of digits is not fixed at 4, then this formula would work (even if there are other items in parentheses in front of it)...

=-("("&TRIM(LEFT(RIGHT(SUBSTITUTE(I2,"(",REPT(" ",20)),20),20)))

 

[Aladin Akyurek]

Yeah, I know... it is neat how Excel thinks a number in parentheses is the negative of what is contained within the parentheses.

By the way, if the number in the parentheses is always at the end, but the number of digits is not fixed at 4, then this formula would work (even if there are other items in parentheses in front of it)...

=-("("&TRIM(LEFT(RIGHT(SUBSTITUTE(I2,"(",REPT(" ",20)),20),20)))

Shorter in calls... I should have done so in the first place... Something like:

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