Extracting Numbers from text string

This formula will extract anything between an opening and closing parenthesis set. Perhaps it will work for you....

=MID(B12,FIND("(",B12)+1,FIND(")",B12)-FIND("(",B12)-1)


assumption, only 1 opening/closing parenthesis set exists in the cell.

Originally Posted by rlane23

I would a formula that will extract a number from a text string. I have written one but if the name in the text includes a number I get a value error.

Example:
Basement Finishing (3630) - this is all in one cell and I want to pull out the number in the parenthesis.

I have written a formula that does this but if, for example, the cell reads Basement 2 Finishing (3630) I get a value error.

Here is the formula (it is an array)

=1*MID(B12,MATCH(TRUE,ISNUMBER(1*MID(B12,ROW($1:$500),1)),0),COUNT(1*MID(B12,ROW($1:$500),1)))

Can anyone help?

Try this:

=MID(SUBSTITUTE(B12,")",""),FIND("(",B12)+1,99)+0

If there's only one occurrence of a number in the string (ie the one you want to extract) then the following is another formula option:

=-LOOKUP(0,-MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps!

Richard


EDIT: Rylo, there are slighly easier ways of writing the pattern if using Regular Expressions, two of which are below:

Please Login or Register  to view this content.

GetNum1 simply extracts the first numerical string of digits it matches in the string. This would work in all the highlighted examples. GetNum2 specifically matches to a sequence of digits ending with a closing bracket ). There could still be times when either of these returned incorrect results but that depends on the structure of the string passed to the function and also exactly what the required return value would be in those cases).