VBA 从字符串/单元格的开头删除数字

VBA to remove numbers from start of string/cell

提问by Philip Connell

I hope you can help.

我希望你能帮忙。

I have a piece of code that is currently removing all the text from the cells in Column G. What I need is for this code to instead of removing the text I would like it to remove the numbers, and I only want it to remove the numbers at the beginning of the string/cellthe rest of the data I would like to remain the same.

我有一段代码，目前正在从 G 列中的单元格中删除所有文本。我需要的是这段代码不是删除文本，而是我希望它删除数字，我只希望它删除字符串/单元格开头的数字其余数据我希望保持不变。

I have attached a picture PIC.1 for betting understanding.

我附上了一张图片 PIC.1 以供投注理解。

PIC1

PIC1

The code I currently have and I hope can be amended is below and as always any and all help is greatly appreciated.

我目前拥有并希望可以修改的代码如下，并且一如既往地非常感谢所有帮助。

CODE

代码

Sub RemoveNonDigits()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String
  Const StartRow As Long = 1
  Const DataColumn As String = "G"
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn)
    For Z = 1 To Len(CellVal)
      If Mid(CellVal, Z, 1) Like "[!0-9]" Then Mid(CellVal, Z, 1) = " "
    Next
    With Cells(X, DataColumn)
      .NumberFormat = "@"
      .Value = Replace(CellVal, " ", "")
    End With
  Next
  Application.ScreenUpdating = True
End Sub

回答by Alex K.

CellVal = LTrimDigits(Cells(X, DataColumn))

With this fairly efficient:

有了这个相当有效：

Public Function LTrimDigits(value As String) As String
    Dim i As Long
    For i = 1 To Len(value) '//loop each char
        Select Case Mid$(value, i, 1) '//examine current char
            Case "0" To "9" '//permitted chars
            Case Else: Exit For '// i is the cut off point
        End Select
    Next
    LTrimDigits = Mid$(value, i) '//strip lead
End Function

回答by Wiktor Stribi?ew

See the modified code below:

请参阅下面的修改代码：

Sub RemoveNonDigits()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String
  Const StartRow As Long = 1
  Const DataColumn As String = "G"
  Application.ScreenUpdating = False
  LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataColumn)
    While IsNumeric(Left(CellVal, 1))   ' Here
      CellVal = Mid(CellVal, 2)         ' all digits at the start 
    Wend                                ' are removed
    Cells(X, DataColumn) = Trim(CellVal)
  Next
  Application.ScreenUpdating = True
End Sub

That is, while the starting char in CellValis a digit, get the substring starting with the second char, and go on until no match.

也就是说，虽然起始字符CellVal是一个数字，但从第二个字符开始获取子字符串，直到没有匹配为止。

回答by Wolfie

This function will strip leading digits and spaces from a string

此函数将从字符串中去除前导数字和空格

Function RemoveLeadingDigits(str As String) As String

    Dim i As Long
    Dim chr As String
    ' Loop through string
    For i = 1 To Len(str)
        ' Get character i
        chr = Mid(str, i, 1)
        ' Keep looping until character is not a number or space
        If Not IsNumeric(chr) And Not chr = " " Then
            ' If it is a number or space, strip checked characters
            ' from str (because they'll be numeric or space)
            str = Right(str, Len(str) - i + 1)
            ' Stop looping as non-numeric characters encountered
            Exit For
        End If
    Next i

    ' Return the value of str
    RemoveLeadingDigits = str

End Function

You can call it from your code by

你可以从你的代码中调用它

Sub RemoveNonDigits()
    Dim X As Long, LastRow As Long, CellVal As String
    Const StartRow As Long = 1
    Const DataColumn As String = "G"
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
    For X = StartRow To LastRow
        CellVal = Cells(X, DataColumn).Value
        ' ----------------------------------------
        CellVal = RemoveLeadingDigits(CellVal)
        ' ----------------------------------------
    Next
    Application.ScreenUpdating = True
End Sub

A note on your code though:

不过，请注意您的代码：

You should really fully qualify your cells. For instance, wrap the whole looping section in With ThisWorkbook.Sheets("YourSheet")and then accessing cells using .Cells(row, col)rather than just Cells(row, col).

你真的应该完全鉴定你的细胞。例如，将整个循环部分包装起来With ThisWorkbook.Sheets("YourSheet")，然后使用.Cells(row, col)而不是仅使用Cells(row, col).

回答by Slai

A bit hacky shorter alternative (assuming all values start with integer)

有点hacky较短的替代方案（假设所有值都以整数开头）

For Each cell in Range([G7], [G7].End(xlDown))

    cell.Value2 = Trim(Mid(cell, Len(Str(Val(cell)))))

Next

回答by Pspl

I think a simple change like this:

我认为一个简单的改变是这样的：

For X = StartRow To LastRow
    Cells(X, DataColumn).Formular1c1 = Application.Trim(Replace(Cells(X, DataColumn).Text, Val(Cells(X, DataColumn).Text), ""))
Next X

will solve your problem...

会解决你的问题...