VBA to remove numbers from start of string/cell

Question

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/cell the rest of the data I would like to remain the same.

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

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

As it appears, the number you want to remove is isolated from the rest by a space. Is that assured? because the solution would be much easier in that case. — A.S.H
– A.S.H, Commented Mar 10, 2017 at 11:16
So, your current code removes all but digits, and you want just the opposite, right? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Mar 10, 2017 at 11:17
If Mid(CellVal, Z, 1) Like "[0-9]" Then Mid(CellVal, Z, 1) = " " else exit for a do until maybe a neater approach. — Nathan_Sav
– Nathan_Sav, Commented Mar 10, 2017 at 11:22
Hi all thank you taking the time to respond @ASH as space after the digit is not assured unfortunately. AT:Wiktor Stibizew. What i need is for the digits at the start to be removed only the rest of the cell content can remain the same. AT:Nathan_Sav I cant follow the code logic unfortunately. Thanks for the the help guys its greatly appreciated. — Philip Connell
– Philip Connell, Commented Mar 10, 2017 at 11:30

Alex K. · Accepted Answer · 2017-03-10 11:36:49Z

5

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

answered Mar 10, 2017 at 11:36

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A.S.H Over a year ago

Efficient implementation with no useless string copies. I love it.

Wiktor Stribiżew · Accepted Answer · 2017-03-10 11:28:53Z

3

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 CellVal is a digit, get the substring starting with the second char, and go on until no match.

answered Mar 10, 2017 at 11:28

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

1 Comment

Philip Connell Over a year ago

BOO YAAHH!! Awesome work Wiktor. Thank you so much for taking the time. This works perfectly. Much respect from Dublin :-) Have a great day and thank you again. :-)

Wolfie · Accepted Answer · 2017-03-10 11:33:48Z

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).

Slai · Accepted Answer · 2017-03-11 23:22:55Z

2

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

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

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

Next

answered Mar 11, 2017 at 23:22

Slai

23k5 gold badges49 silver badges55 bronze badges

Comments

Pspl · Accepted Answer · 2017-03-10 11:25:48Z

1

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...

answered Mar 10, 2017 at 11:25

Pspl

1,47413 silver badges26 bronze badges

2 Comments

Wolfie Over a year ago

Nope, this will also remove the number at the end of the strings, which is the same as the number at the start of the strings! Good thinking though

Philip Connell Over a year ago

yep tried that piece of code. Not working as hoped :-)

Collectives™ on Stack Overflow

VBA to remove numbers from start of string/cell

5 Answers 5

1 Comment

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related