Extract numbers after specific text in a text string

Re: Extract numbers after specific text in a text string

Would this technique work for you?
http://chandoo.org/wp/2012/06/…-numbers-from-text-excel/

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.

Function ExtractNumber(rng As Range)
    Dim i As Integer, str As String, CharPos As Long
    str = rng.Value
    CharPos = InStr(1, str, "SN", vbTextCompare) + 2
    If CharPos > 2 Then
        For i = CharPos To Len(rng)
            Select Case Asc(Mid(rng.Value, i, 1))
            Case 48 To 57
                ExtractNumber = Trim(ExtractNumber & Mid(rng.Value, i, 1))
            End Select
        Next i
    Else: ExtractNumber = ""
    End If
End Function

Re: Extract numbers after specific text in a text string

Hi samshan143,
For a formula use
value to used in a1
in b1 =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

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

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.