寻求更高效Excel VBA正则表达式：规整多线垃圾文本

需求清单 #

• 去除文本首尾所有空白字符（含非断空格\xA0）及空行
• 将中间连续多空行（包括仅含空白字符的行）合并为1个空行
• 删除文本内冗余空格（含非断空格），统一为单个空格
• 清理冗余逗号（如连续逗号、逗号前后冗余空格）
• 删除相邻的重复单词（忽略大小写）

待处理文本示例（A1单元格） #

A1 = see below
It's difficult to input because the multiple lines, but I will type some text that shows, imagine this below as in cell A1:
           
            
           
          
         Several blank lines with spaces before this text and now this. Mark     is a  really      nice guy.       And     
             there is a lot of              
                
       
            
                
       white     space all     over the.     place, and and    ,  , ,,,,,  lots of commas,  ,   , 

      
                    
and , text   text  Text before and  after , commas
          
                         
                      
            many blank characters on every single blank line
                
           
         
              

预期处理结果（F1单元格） #

Several blank lines with spaces before this text and now this. Mark is a really nice guy. And
there is a lot of      
 
white space all over the. place, and, lots of commas,
 
and, text before and after, commas
 
many blank characters on every single blank line

现有实现方案 #

分步处理公式 #

B1 = RegExp(A1, "(\r?\n\s*){2,}", CHAR(10) & CHAR(10), TRUE, TRUE, TRUE)
C1 = RegExp(B1, "^(?:[\t\s\xA0]+)|(?:[\t\s\xA0]+)$", "", TRUE, FALSE, TRUE)
D1 = RegExp(C1, "[ \xA0]+", " ", TRUE, TRUE, TRUE)
E1 = RegExp(D1, "[ ,]{2,}", ", ", TRUE, TRUE, TRUE)
F1 = RegExp(E1, "\b(\w+)\b[\s\xA0]+(?=\1\b)", "", TRUE, FALSE, TRUE)

VBA正则函数 #

Public Function RegExp(ByVal text As String, _
    ByVal pattern As String, _
    ByVal strReplace As String, _
    Optional ByVal caseInsensitive As Boolean = True, _
    Optional ByVal multiLines As Boolean = True, _
    Optional ByVal allOccurrences As Boolean = True) As String
    Dim regEx As New RegExp
    With regEx
        .pattern = pattern
        .MultiLine = multiLines
        .IgnoreCase = caseInsensitive
        .Global = allOccurrences
    End With
    RegExp = regEx.Replace(text, strReplace) 'returns modified text (or original text if no modifications)
End Function

优化后的精简正则方案 #

将原5步压缩为4步，合并重复逻辑，提升处理效率：

步骤1：清理首尾空白+合并连续空行 #

B1 = RegExp(A1, "^[\s\xA0\r\n]+|[\s\xA0\r\n]+$|(\r?\n\s*){2,}", IIf(InStr(RegExp(A1, "^[\s\xA0\r\n]+|[\s\xA0\r\n]+$", ""), vbLf) > 0, vbLf & vbLf, ""), TRUE, TRUE, TRUE)

正则说明：

• ^[\s\xA0\r\n]+：匹配文本开头所有空白、非断空格、换行
• [\s\xA0\r\n]+$：匹配文本结尾所有空白、非断空格、换行
• (\r?\n\s*){2,}：匹配2次及以上连续换行（含换行后空白）
• 替换规则：首尾匹配替换为空，连续空行替换为2个换行符（对应单个空行）

步骤2：统一冗余空格为单个空格 #

C1 = RegExp(B1, "[ \xA0]+", " ", TRUE, TRUE, TRUE)

正则说明：将所有连续空格（含非断空格）替换为单个空格

步骤3：清理冗余逗号与空格组合 #

D1 = RegExp(C1, "(,\s*)+|(\s+,)+", ", ", TRUE, TRUE, TRUE)
D1 = RegExp(D1, ",\s*$", "", TRUE, FALSE, TRUE)

正则说明：

• (,\s*)+：匹配连续逗号（含逗号后空格）
• (\s+,)+：匹配逗号前的连续空格
• 统一替换为, 后，再清理文本末尾可能残留的多余逗号

步骤4：删除相邻重复单词 #

F1 = RegExp(D1, "\b(\w+)\b\s+(?=\1\b)", "", TRUE, FALSE, TRUE)

正则说明：匹配相邻的重复单词（忽略大小写），保留第一个，删除后续重复项

更极致的3步方案 #

若进一步合并逻辑，可将步骤2和3合并：

B1 = RegExp(A1, "^[\s\xA0\r\n]+|[\s\xA0\r\n]+$|(\r?\n\s*){2,}", IIf(InStr(RegExp(A1, "^[\s\xA0\r\n]+|[\s\xA0\r\n]+$", ""), vbLf) > 0, vbLf & vbLf, ""), TRUE, TRUE, TRUE)
C1 = RegExp(B1, "[ \xA0]+|(,\s*)+|(\s+,)+", IIf(Left(RegExp(B1, "[ \xA0]+", " "), 1) = ",", ", ", " "), TRUE, TRUE, TRUE)
C1 = RegExp(C1, ",\s*$", "", TRUE, FALSE, TRUE)
F1 = RegExp(C1, "\b(\w+)\b\s+(?=\1\b)", "", TRUE, FALSE, TRUE)

内容的提问来源于stack exchange，提问作者Mark Main