时间戳

首页 > 解决方案 > 从文本中清除数字并粘贴为外部工作簿中的值

excel - 从文本中清除数字并粘贴为外部工作簿中的值

问题描述

我正在寻找我的项目的解决方案。我有源数据workbook_src和目标worbook_dest。Inworkbook_src是格式中的数字="123456789"。我的目标是以workbook_src只读方式打开，将带有数字的行复制到内存中，仅将它们清除为数字并将特殊粘贴workbook_dest为值。

workbook_src以只读方式打开

Dim src As Workbook
Set src = Workbooks.Open("C:\xxx.xlsx", True, True)
ThisWorkbook.Activate

清除数字的功能

Function GetNumeric(CellRef As String)
    Dim StringLength As Integer
    StringLength = Len(CellRef)
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
    Next i 
    GetNumeric = Result
End Function

但我有问题将此功能应用于整行

标签： excelvba

解决方案

找到解决方案

Function cleanString(str As String) As String
    Dim ch, bytes() As Byte: bytes = str
    For Each ch In bytes
        If Chr(ch) Like "[A-Z.a-z 0-9]" Then cleanString = cleanString & Chr(ch)
    Next ch
End Function

Sub btn_CleanAll()
    Dim rng As Range

    For Each rng In Sheets("Sheet1").Range("A2:A30000").Cells
    rng.Value = cleanString(rng.Value)
    Next
    MsgBox "Done"
End Sub

推荐阅读