|
想将复制粘贴在表格中的文字和文字后的;留下,找到了留文字的代码,但只留下了文字,想把;也留下,求解!谢谢啦!
如单元格中内容为:aaaa你好; bbbb不好; cccc挺好;(注释;为英文的分号,分号后面有空格)
希望留下的为:你好;不好;挺好;或者你好+空格+不好+空格+挺好+空格也行
留文字代码如下
Sub 留汉字()
Dim X, i, j
On Error GoTo ren
Application.ScreenUpdating = False
Dim RegEx, Cel
Set RegEx = CreateObject("VBSCRIPT.REGEXP") 'RegEx为建立正则表达式
RegEx.Global = True '设置全局可用
RegEx.Pattern = "[^\u4e00-\u9fa5]"
a = Selection.Address
If a = "" Then Exit Sub
arr1 = Split(a, "$")
If arr1(1) Like "*:" Then
If IsNumeric(Mid(arr1(1), 1, Len(arr1(1)) - 1)) Then
Y = ActiveSheet.UsedRange.Column
X = ActiveSheet.UsedRange.Columns.Count + Y - 1
Y = Split(Cells(1, Y).Address, "$")(1)
X = Split(Cells(1, X).Address, "$")(1)
Z = ActiveSheet.UsedRange.Rows.Count
n = arr1(2)
If Z < n Then arr1(2) = Z
a = "$" & Y & "$" & arr1(1) & "$" & X & "$" & arr1(2)
Else
a = "$" & Mid(arr1(1), 1, Len(arr1(1)) - 1) & "$" & "1:$" & arr1(2) & "$" & ActiveSheet.UsedRange.Rows.Count
End If
End If
If Range(a).Rows.Count + Range(a).Columns.Count > 2 Then
arr = Range(a).Value
For i = 1 To UBound(arr, 2)
For j = 1 To UBound(arr)
arr(j, i) = RegEx.Replace(arr(j, i), "")
Next j
Next i
Range(a).Value = arr
Else
Range(a) = RegEx.Replace(Range(a), "")
End If
Application.ScreenUpdating = True
ren:
End Sub
|
|