Excel VBA文本处理的神操作！

Excel VBA文本处理的神操作！

引用

CSDN

等

9

来源

1.

https://wenku.csdn.net/column/732hsya0ys

2.

https://blog.csdn.net/m0_62872215/article/details/134819913

3.

https://blog.csdn.net/tuzajun/article/details/130377466

4.

https://blog.csdn.net/keepiss/article/details/82597307

5.

https://blog.csdn.net/jianfengxia/article/details/78519610

6.

https://www.cnblogs.com/cloudtj/articles/5363646.html

7.

https://www.kdocs.cn/article/E552464E77.html

8.

https://my.oschina.net/emacs_8705838/blog/17056688

9.

https://www.cnblogs.com/KL58/articles/17322008.html

在Excel中，掌握VBA文本处理函数可以让你的工作效率飞速提升。无论是LEN、MID还是LEFT和RIGHT函数，都能帮你轻松搞定各种文本数据的操作。这些实用技巧不仅能提高你的工作效率，还能让你在同事面前大显身手。快来学习这些Excel VBA文本处理的神操作吧！

1. 获取字符串长度：Len函数

假设你有一个字符串"Hello World"，想要知道它的长度，可以使用Len函数：

Sub GetStringLength()
    Dim myString As String
    myString = "Hello World"
    Dim strLen As Integer
    strLen = Len(myString)
    Debug.Print strLen
End Sub

运行结果：

2. 查找子字符串：InStr函数

InStr函数可以帮你查找一个字符串是否包含特定的子字符串。例如，检查"Hello World"中是否包含"World"：

Sub FindSubstring()
    Dim myString As String
    myString = "Hello World"
    Dim myExp As String
    myExp = "World"
    Dim index As Integer
    index = InStr(myString, myExp)
    If index > 0 Then
        Debug.Print "找到子字符串，位置：" & index
    Else
        Debug.Print "未找到子字符串"
    End If
End Sub

运行结果：

3. 截取字符串：Left、Mid、Right函数

这三个函数分别用于从字符串的左边、中间和右边截取指定长度的字符。例如：

Sub SliceString()
    Dim myString As String
    myString = "Start diandian didi end"
    Dim startIndex As Integer
    startIndex = 3
    Dim sliceLen As Integer
    sliceLen = 5
    Dim leftChar As String
    leftChar = Left(myString, sliceLen)
    Debug.Print "左边5个字符：" & leftChar
    Dim rightChar As String
    rightChar = Right(myString, sliceLen)
    Debug.Print "右边5个字符：" & rightChar
    Dim midChar As String
    midChar = Mid(myString, startIndex, sliceLen)
    Debug.Print "中间5个字符：" & midChar
End Sub

运行结果：

4. 替换字符串：Replace函数

Replace函数可以将字符串中的特定字符替换为其他字符。例如，将"Hello World"中的"World"替换为"Excel"：

Sub ReplaceString()
    Dim myString As String
    myString = "Hello World"
    Dim findChar As String
    findChar = "World"
    Dim replaceChar As String
    replaceChar = "Excel"
    Dim replaceResult As String
    replaceResult = Replace(myString, findChar, replaceChar)
    Debug.Print replaceResult
End Sub

运行结果：

5. 分割字符串：Split函数

Split函数可以将字符串按指定字符分割成数组。例如，将"apple,banana,cherry"按逗号分割：

Sub SplitString()
    Dim myString As String
    myString = "apple,banana,cherry"
    Dim splitChar As String
    splitChar = ","
    Dim splitResults As Variant
    splitResults = Split(myString, splitChar, -1)
    Dim result As Variant
    For Each result In splitResults
        Debug.Print result
    Next
End Sub

运行结果：

1. 多Sheet数据整合

在实际工作中，我们经常需要将多个工作表的数据整合到一个工作表中。以下是一个示例代码：

Sub ImportDataFromSheets()
    Dim ws As Worksheet
    Dim wsMain As Worksheet
    Dim i As Integer
    Set wsMain = ThisWorkbook.Sheets.Add
    wsMain.Name = "CombinedData"
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> wsMain.Name Then
            ws.Rows(1).Copy Destination:=wsMain.Cells(1, 1)
            For i = 2 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
                ws.Rows(i).Copy Destination:=wsMain.Cells(wsMain.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1, 1)
            Next i
        End If
    Next ws
End Sub

2. 文本文件读写

VBA还可以处理文本文件的读写操作。以下是一个将Excel数据写入txt文件的示例：

Sub Data_Output()
    Dim Map_i As Integer, M As Integer, N As Integer
    Dim shRaw As Object
    Dim Bookfile As String
    Bookfile = ThisWorkbook.Name
    Set shRaw = Workbooks(Bookfile).Sheets("Raw Data")
    Dim FilePath As String
    FilePath = "D:\New folder"
    Dim filename As String
    filename = FilePath & "\" & Date$ & ".txt"
    Open filename For Output As #1
    For Map_i = 0 To 33
        Print #1, shRaw.Cells(2 + Map_i, "EI")
    Next Map_i
    For M = 0 To 136
        Dim Line_Map As String
        For N = 0 To 136
            If shRaw.Cells(M + 1, N + 1) <> "" Then
                Dim Tem_Map As String
                Tem_Map = shRaw.Cells(M + 1, N + 1)
                Line_Map = Line_Map & Tem_Map
            End If
        Next N
        Print #1, Line_Map
    Next M
    Close #1
    ActiveWorkbook.Save
End Sub

1. 字符串相似度计算：StrSimilarity

这个函数可以计算两个字符串的相似度，基于Levenshtein算法：

Public Function StrSimilarity(ByVal s1 As String, ByVal s2 As String, _
    Optional ByVal ignoreCase As Boolean = True) As Double
    ' [实现代码...]
End Function

Sub SimilarityDemo()
    Debug.Print StrSimilarity("apple", "appel")  ' 返回 ≈0.8
End Sub

2. 模糊匹配：StrFuzzyMatch

这个函数可以实现基于编辑距离的模糊匹配：

Public Function StrFuzzyMatch(ByVal source As String, ByVal target As String, _
    Optional ByVal maxDistance As Integer = 3) As Boolean
    ' [实现代码...]
End Function

Sub FuzzyMatchDemo()
    Debug.Print StrFuzzyMatch("Microsoft", "Microsft", 2)  ' 返回 True
End Sub

3. Soundex编码：StrSoundex

这个函数可以生成字符串的Soundex编码，用于语音相似度比较：

Public Function StrSoundex(ByVal s As String) As String
    ' [实现代码...]
End Function

Sub SoundexDemo()
    Debug.Print StrSoundex("Robert")  ' 返回 R163
End Sub

4. 移除重复字符：StrRemoveDuplicates

这个函数可以移除字符串中的重复字符：

Public Function StrRemoveDuplicates(ByVal s As String, _
    Optional ByVal minRepeat As Integer = 2) As String
    ' [实现代码...]
End Function

Sub RemoveDuplicatesDemo()
    Debug.Print StrRemoveDuplicates("aaaaabbbcc")  ' 返回 "abc"
End Sub

5. 子字符串计数：StrCountSubstring

这个函数可以统计子字符串在源字符串中出现的次数：

Public Function StrCountSubstring(ByVal source As String, ByVal substring As String, _
    Optional ByVal ignoreCase As Boolean = True) As Long
    ' [实现代码...]
End Function

Sub CountSubstringDemo()
    Debug.Print StrCountSubstring("Hello World", "o")  ' 返回 2
End Sub

6. 正则表达式替换：StrRegexReplace

这个函数支持使用正则表达式进行高级替换操作：

Public Function StrRegexReplace(ByVal source As String, ByVal pattern As String, _
    ByVal replacement As String, Optional ByVal ignoreCase As Boolean = True) As String
    ' [实现代码...]
End Function

Sub RegexReplaceDemo()
    Debug.Print StrRegexReplace("Tel:123-4567", "\D", "")  ' 返回 "1234567"
End Sub

7. 智能分词：StrSplitWords

这个函数可以实现中英文混合文本的智能分词：

Public Function StrSplitWords(ByVal s As String, _
    Optional ByVal keepPunctuation As Boolean = False) As Variant
    ' [实现代码...]
End Function

Sub SplitWordsDemo()
    Dim words As Variant
    words = StrSplitWords("Hello, 世界！")
    Dim word As Variant
    For Each word In words
        Debug.Print word
    Next
End Sub

8. 余弦相似度：StrCosineSimilarity

这个函数可以计算两个字符串的余弦相似度：

Public Function StrCosineSimilarity(ByVal s1 As String, ByVal s2 As String) As Double
    ' [实现代码...]
End Function

Sub CosineSimilarityDemo()
    Debug.Print StrCosineSimilarity("Hello World", "World Hello")  ' 返回 1
End Sub

通过掌握这些基础和高级的VBA文本处理函数，你可以轻松应对各种复杂的文本处理任务。无论是简单的字符串操作，还是复杂的文本分析，VBA都能帮你高效完成。希望这些技巧能为你的工作带来便利！