 # Extract numbers after specific text in a text string

• Hi,

I am trying to extract numbers after a specific text in a text, for eg :

abc SN 12345 xyz
edf SN No. 456 mno

As per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)

Any formula to get result as "12345" and "456".

please prefer formula instead of VBA, since i am not sure how to use VBA.

thanks

• Re: Extract numbers after specific text in a text string

Hi..

Add this code to a Module and use it like a normal worksheet function..

Example: Typing =ExtractNumber(A1) into a cell will extract the number you want from Cell A1.

• Re: Extract numbers after specific text in a text string

The formula above collects all the numbers in the string, for example, for "abc 23 SN 12345 xyz" provides "2312345". Moreover, if "SN" occurs in words it should be ignored.

Try this formula, if strings are in col A, put into B1:

=LOOKUP(10^6,1*MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",FIND(" SN "," "&A1&" "))),{2,3,4,5,6}))

• Re: Extract numbers after specific text in a text string

Quote from István Hirsch;705823

The formula above collects all the numbers in the string, for example, for "abc 23 SN 12345 xyz" provides "2312345". Moreover, if "SN" occurs in words it should be ignored.

Try this formula, if strings are in col A, put into B1:

=LOOKUP(10^6,1*MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",FIND(" SN "," "&A1&" "))),{2,3,4,5,6}))

Dear Istvan Hirsch,

your formula works perfectly thanks.. However, i suppose instead of FIND(" SN ",....) i can use SEARCH (" sn ",...) since at times SN could be small letters as well ? btw is spaces required in " SN " ?

• Re: Extract numbers after specific text in a text string

Hi Istvan,

Can u please explain how the below formula works, i am bit new to such big formulas... thx...

Quote from samshan143;705846

Dear Istvan Hirsch,

your formula works perfectly thanks.. However, i suppose instead of FIND(" SN ",....) i can use SEARCH (" sn ",...) since at times SN could be small letters as well ? btw is spaces required in " SN " ?

• Re: Extract numbers after specific text in a text string

LOOKUP(10^6,1*MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",FIND(" SN "," "&A1&" "))),{2,3,4,5,6}))

The core of this formula is FIND (of course it can be changed to SEARCH) in the form FIND (find text/now green/, within text/now red/, [start num]/now blue/). Blue part states that the search should be started from the location where „ SN „ is found and not from the very beginning of the string. From this location we want to find the first digit (in green). The formula will search for all the digits in green in A1 from the position of „ SN „. A1 is concatenated with all the digits, otherwise FIND function would give error if it could not find any of the digits searched for in the string. Now we have the distance of each digit in the number following „ SN „ in characters: MIN function chooses from this data the lowest, that is, determines the position of the digit which is closest to „ SN „. This is the position of the first digit in MID function, and the length of the number is set to 2-6. Finally, LOOKUP function checks the numbers after one another, for example 12 (first two), 123 (first three etc.), 1234, 12345 and selects the highest which is lower than 10^6.

If we searched for „SN” and not for „ SN „, we could get false positive for example at MSN 12 (12), or SNOWBOARD 21 (21).

• Re: Extract numbers after specific text in a text string

Istvan Hirsch, Thanks and appreciate it....

• Thank you István Hirsch as this formula worked like a charm. I am have a slight different issue and it will be really great if you can help me out.

In the cell where I am using the formula to search I have two keywords AVolume# & Volume# and because the only difference is A it is not giving the correct result. Is there a way to search for an exact match

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!