# 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

Name | grammar | Meaning |
---|---|---|

Positive sequence look around | (?=abc) | On the right is abc. |

Negative order look around | (?!abc) | It's not abc on the right |

Positive reverse order look around | (?<=abc) | On the left is abc. |

Negative reverse order look around | (?<!abc) | Not abc on the left |

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 >

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