Topic 1

Set output format as required

Option Explicit
Sub Company()

    Dim i, s
    i = 1222.222223
    s = Format(i, "$.###")
    '#For one digit
    MsgBox s
    
End Sub

Option Explicit
Sub Company()

    Dim i, s
    i = 1222.2
    s = Format(i, "$.0000")
    '0 For one digit,Replace the insufficient digits with 0
    'If the format is 00.0000
    'The result is 1222.2000 Not 22.2000
   	'#Empathy
    MsgBox s
    
End Sub

Positive number, negative number, zero and blank respectively

Sub format2()
    
    Dim i, s
    For i = 2 To 5
        s = Format(Cells(i, 2), "$.000;($.000);Zero;-")
        'First positive, second negative, third 0, fourth empty
        'Semicolon separated, no quotes in the middle
        Cells(i, 3) = s
    Next i
    
End Sub

Format for date

Sub Date format()

    Dim s As String, d As Date
    d = Range("b2").Value
    s = Format(d, "m Yue di d day(Ad yyyy year),dddd")
 	Cells(3, 2) = s
    
End Sub

Topic two

Multiple cycles + regular expression breaking complex structure

Review regular expressions briefly

Sub regular expression 1()
    Dim i As Long, s As String, myreg As Object
    Dim mymatches As Object, mymatch As Object
    
    s = Range("B2").Value
    Set myreg = CreateObject("vbscript.regexp")
    'Introducing regular expression function
    myreg.Global = True
    'Introducing regular expression code
    myreg.Pattern = "(\d+)-(\d+)"
    'Some figures-Some figures
    Set mymatches = myreg.Execute(s)
    
    i = 8
    
    For Each mymatch In mymatches
    'Cycle scan two capture groups
        Cells(i, 2) = mymatch.submatches(0)
        Cells(i, 3) = mymatch.submatches(1)
        i = i + 1
    Next mymatch
       
End Sub

Topic upgrading
Zhang San Tel 0411-82373923-2-1 Li Si Tel 0571-233256343-1-1-1 Wang Wu Tel 021-62344356-1

Match out

Sub regular expression 2()
    Dim i, j As Long, s As String
    Dim mymatches1 As Object, myreg1 As Object
    Dim mymatches2 As Object, myreg2 As Object
    s = Range("B3").Value
    'First regular expression, find the full phone number
    Set myreg1 = CreateObject("vbscript.regexp")
    myreg1.Global = True
    myreg1.Pattern = "\d+(-\d+)+"
    
    'The second regular expression, finding consecutive numbers in a number
    Set myreg2 = CreateObject("vbscript.regexp")
    myreg2.Global = True
    myreg2.Pattern = "\d+"
    'Execute first regular expression
    Set mymatches1 = myreg1.Execute(s)
    'Loop read and hand over to the second
    For i = 0 To mymatches1.Count - 1
    'Perform a second expression on the number found
        Set mymatches2 = myreg2.Execute(mymatches1(i).Value)
        'Loop read no brother continuous number
        For j = 0 To mymatches2.Count - 1
            Cells(i + 7, j + 2) = mymatches2(j).Value
        Next j
        
        
    Next i
       
End Sub

Non capture group:
Exclude meaningless capture groups, only group, not capture
Change (content) to (?: content)
Only the first and the last

Topic three

Problem requirements: solve the following text decomposition, find the names of each site
Beijing West Beijing station Beijing East Beijing South Station Beijing East

This requires a look around

VBA only supports sequential look

Sub Look around()

    Dim i, j As Long, s As String
    Dim mymatches As Object, myreg As Object

    s = Range("B3").Value
    'First regular expression, find the full phone number
    Set myreg = CreateObject("vbscript.regexp")
    myreg.Global = True
    myreg.Pattern = "Beijing\S+?(?=Beijing|$)"
    'Beijing, any non empty character, Beijing on the right, or the end
    Set mymatches = myreg.Execute(s)

    For i = 0 To mymatches.Count - 1
        Cells(i + 1, 3) = mymatches(i).Value
    Next i

End Sub

You can solve this problem more often

Sub Look around()

    Dim i As Long
    Dim a() As String, s
    a = Split(Range("b3").Value, "Beijing")
    'String to"Beijing"Separate for separator,Returned a Is a one-dimensional array
    i = 2
    For Each s In a
        If s <> "" Then
            Cells(i, 1) = "Beijing" & s
            'Split and then splice
            i = i + 1
        End If
    Next s
    
End Sub

Of course, this question can be spliced in Beijing. Generally, look around is the choice

Topic four

Original amount: 5321200213 yuan, 4230 yuan, 50220 yuan
Add one in the middle of every 4 bits, split
(? < = \ d) (? = (\ D {4}) + yuan)
One digit on the left, four digits on the right, plus a dollar

Here is the replacement, which is to add a comma between the two capture groups
But because VBA does not support reverse looking, the code needs to be modified

Matching target
A number and its following position the position is characterized by 4n numbers on the right and ending with a dollar
Replacement process
Replace the number and position matched above with that number (i.e., content in the first capture group, $1), and a comma

Option Explicit

Sub Demo()

    Dim reg As Object, i As Long, s As String
    
    Set reg = CreateObject("vbscript.regexp")
    reg.Global = True
    reg.Pattern = "(\d)(?=(\d{4})+element)"
    
    'Execute the regular expression and write the matching results in the A Column row
    For i = 3 To 7
        s = Cells(i, 2)
        Cells(i, 3) = reg.Replace(s, "$1,")
    Next i

End Sub

Topic five

Some points for attention in text grabbing

<a href=[\s\S]*?>([\s\S]*?)</a>

[\ s\S] includes one character, including all characters, including line breaks
If you write. *, you can't process a new line. You can only process one line. You must use lazy search here
It can also be written as

<a href=[^>]*>([\s\S]*?)</a>

Search until you meet >

1. Unique ID found
2. Simplify irrelevant characters: beware of greedy search and line feed
3. Design multi-level regular expressions according to the situation