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


    How about a UDF?


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

  • 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

  • RDaga, welcome to Ozgrid.


    Please read the Forum Rules and you will see that members are asked not to post questions in another member's thread. Please start your own question.

  • royUK

    Closed the thread.