如何删除单元格中隐藏的字符

ykcbf1100

1. Sub ykcbf()  '//2025.3.26   清除不可见字符
   
2.     Set Rng = ActiveSheet.UsedRange
   
3.     For Each k In Rng
   
4.         k.Value = ston(k.Value)
   
5.     Next
   
6.     MsgBox "OK！"
   
7. End Sub
   
8. 
   
9. Function ston(st)
   
10.     Set reg = CreateObject("VBScript.Regexp")
    
11.     st = Replace(st, "   ", "")
    
12.     st = WorksheetFunction.Trim(st)
    
13.     With reg
    
14.         .Pattern = "[\s\x7F-\xA0]+"
    
15.         ston = .Replace(st, "")
    
16.     End With
    
17. End Function
    

复制代码

PZB0000

=CLEAN(D2:D12)我这里wps没问题
另一个方法是选择这些有不可见字符的单元格，左上角，清空前后字符串