EXCEL-VBA Regular Expression Extract text case

The background is this, I have a table of statistics, need to be the IP address of the provinces and cities to be extracted, in order to deal with. So I first thought about the regular expression in Python, and I was going to write a custom function to fetch it in bulk. However, I did not learn the regular expression syntax in VBA, so I went to search the Internet, and found that it could not run at all. After several rounds, finally fix, so summarize, for later reference.

Programming Purpose: To realize the extraction of city and province information in IP address.

Key points of Knowledge: VBA functions, regular expressions

The code is as follows:

Function Extraction Provinces (rng as Range, name)        application.volatile    Set regx = CreateObject ("VBScript.RegExp")        with REGX        . Global = True        . Pattern = "[\u4e00-\u9fa5]+"        Set mat =. The Execute (RNG)    End with        the Select Case name            "province"            extracts the provinces and cities = Mat. Item (0). Value case                    "City"            extracts provinces and cities = Mat. Item (1). Value case                Else            MsgBox ("Incorrect input")                end Select    End Function

Code Explanation:

1, rng as Range, Name: Pass two parameters, the first parameter is a cell parameter.

2, application.volatile Set regx = CreateObject ("VBScript.RegExp"): Create regular Expression object, fixed syntax.

3. With REGX

. Global = True

. Pattern = "[\u4e00-\u9fa5]+"

Set mat =. Execute (RNG)

End with

Global: Indicates whether to retrieve globally, and true indicates that all matching results are returned. False to return only the first matching result.

Pattern = "[\u4e00-\u9fa5]+" is the regular expression, where [\u4e00-\u9fa5]+ means matching the Chinese string.

4, Set mat =. Execute (RNG): Executes the regular expression. Returns a collection of matchcollection types that match all the results.

5, Next is a multi-conditional judgment statement, the equivalent of If ... Elif ... Else ... End If.

6, the extraction of provinces and cities = Mat. Item (0). Value: In front we mentioned that Mat is a collection of matchcollection types, mat. The Item (0) reads the first matching result, and value indicates that it is read. Finally, the value is assigned to the function. You can also use the following value.

EXCEL-VBA Regular Expression Extract text case