Posts by István Hirsch

    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


    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: Search User Input String in variants of string - find all in any order contained


    Hope you can use this as a UDF. Instead of "OK" in the code you can use, for example, the original user input (now: Professional Land Fees)


    Function FindPerm(c As String) As String
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(Professional)|(Land)|(Fees)" 'Insert more words with similar structure
    FindPerm = .Replace(c, "#")
    .Pattern = "^# # #$" 'Insert more # with space
    If .Test(FindPerm) Then
    FindPerm = .Replace(FindPerm, "OK")
    Else
    FindPerm = ""
    End If
    End With
    End Function

    Re: Extracting Post Code and Locality


    This seems to work for the sample given. Check Microsoft VBSript Regular Expressions 5.5 at Tools/References.


    Code
    1. Function ExtractAddress(c As String) As String
    2. With CreateObject("VBScript.RegExp")
    3. .Pattern = "[\d]+[\D]+$"
    4. If .Test(c) Then ExtractAddress = .Execute(c)(0)
    5. End With
    6. End Function