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: Delete first "," (comma) from left in a alphanumeric string..

This formulas also work:

To delete the first comma:

=SUBSTITUTE(A1,",","",1)

To delete the last comma:

=SUBSTITUTE(A1,",","",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))

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.

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