Excel公式与宏粘贴文本失效：手动输入正常，返回#VALUE/FALSE

问题现象 #

• 手动输入文本到单元格A1时，Excel公式和自定义宏均可正常运行
• 从PDF复制文本到记事本，再粘贴至A1后出现异常：
  • 公式 =ISNUMBER(SEARCH("customers' inventories in January is",A1)) 返回 FALSE
  • 自定义宏 REPLACETEXTS 返回 #VALUE!

问题原因 #

从PDF复制的文本通常会携带不可见的特殊字符（如非打印控制字符、全角空格、Unicode特殊空格等），这些字符肉眼无法分辨，但会破坏字符串的精确匹配，同时导致宏处理时因字符编码问题报错。

解决方法 #

1. 用公式清理文本后匹配 #

使用内置函数组合清理A1中的特殊字符，再执行匹配：

=ISNUMBER(SEARCH("customers' inventories in January is",CLEAN(TRIM(SUBSTITUTE(A1,CHAR(160),"")))))

• CHAR(160)：替换PDF中常见的非断行空格（全角空格）
• TRIM：清除文本首尾及中间的多余普通空格
• CLEAN：移除所有非打印控制字符

2. 修改自定义宏，增加字符清理逻辑 #

在宏中添加文本预处理步骤，同时修正原代码中的逻辑错误：

Function REPLACETEXTS(strInput As String, rngFind As Range, rngReplace As Range) As String
    Dim strTemp As String
    Dim strFind As String
    Dim strReplace As String
    Dim cellFind As Range
    Dim lngColFind As Long, lngRowFind As Long
    Dim lngRowReplace As Long, lngColReplace As Long
    
    ' 预处理：清理输入文本中的特殊字符
    strTemp = CleanSpecialChars(strInput)
    
    lngColFind = rngFind.Columns.Count
    lngRowFind = rngFind.Rows.Count
    lngColReplace = rngReplace.Columns.Count ' 修正原代码错误：应取替换区域的列数
    lngRowReplace = rngReplace.Rows.Count
    
    ' 判断查找与替换区域的尺寸是否匹配
    If Not ((lngColFind = lngColReplace) And (lngRowFind = lngRowReplace)) Then
        REPLACETEXTS = CVErr(xlErrNA)
        Exit Function
    End If
    
    ' 执行批量替换
    For Each cellFind In rngFind
        strFind = cellFind.Value
        strReplace = rngReplace(cellFind.Row - rngFind.Row + 1, cellFind.Column - rngFind.Column + 1).Value
        strTemp = Replace(strTemp, strFind, strReplace)
    Next cellFind
    
    REPLACETEXTS = strTemp
End Function

' 辅助函数：清理各类特殊字符
Private Function CleanSpecialChars(str As String) As String
    ' 替换非断行空格（PDF常见）
    str = Replace(str, Chr(160), " ")
    ' 移除所有非打印字符
    str = Application.Clean(str)
    ' 清理多余空格
    str = Application.Trim(str)
    CleanSpecialChars = str
End Function

3. 粘贴时选择无格式文本 #

粘贴文本时右键选择选择性粘贴→无格式文本，可直接过滤大部分PDF携带的特殊格式字符，减少后续清理工作。

内容的提问来源于stack exchange，提问作者DK's little contribution