VBA fro removing number from the beginning of a cell

[Conell8383]

Hi all. I hope you can help.

I need a macro that can remove numbers from the beginning of a cell only. I have seen many posts on here but none that solve my issue. The issue I am facing is that in Column G I have unwanted numbers at the start of every cell I would like some code that can go through column G remove only the numbers at the start of the cell.

I have attached a picture for better understanding.

I have found some code on here but i deletes TEXT and leaves numbers can this be modified?

CODE

Public Function PullOnly(strSrc As String, CharType As String)
Dim RE As RegExp
Dim regexpPattern As String
Set RE = New RegExp
CharType = LCase(CharType)
Select Case CharType
    Case Is = "digits":
        regexpPattern = "\D"
    Case Is = "letters":
        regexpPattern = "\d"
    Case Else:
        regexpPattern = ""
End Select
RE.Pattern = regexpPattern
RE.Global = True
PullOnly = RE.Replace(strSrc, "")
End Function
 
Sub LeaveNumbers()
Dim cCell As Range
For Each cCell In Selection
    If cCell <> "" Then
        cCell.Value = "'" & PullOnly(cCell.Text, "digits")
    End If
Next cCell
End Sub

As always any and all help is greatly appreciated.

 

[Lobsterboy1]

Do you need to do it with code or could you just insert another column with a formula in and copy the formula down, you could even hide the original column if you want.

In the new column you could use the formula =RIGHT(G5, LEN(G5) - FIND(" ",G5,1))

 

[CsJHUN]

Hi Conell

Its a simple loop-thru macro, test it on sample set, not original data

Sub conell()
lastcell = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
Do
c = c + 1
Text = Range("G6").Offset(c, 0).Value
Range("G6").Offset(c, 0).Value = Mid(Text, InStr(Text, " ") + 1)
Loop Until c = lastcell - 6
End Sub

This will read the cell value from G7 and write back it without the first numbers

Good luck
J

 

[Conell8383]

@CsJHUN: Thank you so much this worked perfectly. You have made my Friday Thank you again for the help. Much Respect from Dublin. Have a great day and thanks again.

 

[Conell8383]

Hi Lobsterboy1 Thank you for taking the time to respond it is greatly appreciated. To answer your question yes I need code and CsJHUN provided it for me. Thank you again for your efforts. Have a great day.

 

[CsJHUN]

Hi, im glad its working as you expected!

If there is a larger number of rows, i suggest you could try the code below. This read all the cellvalues into an array then remove anything before first "space".
On my sample dataset it was 0.5s faster

Sub conell_array()
Application.ScreenUpdating = False
Dim g_arr As Variant
Dim j As Long
lc = ActiveSheet.Range("G" & Rows.Count).End(xlUp).Row
Set g_arr = Range("G6:G" & lc)
For j = 1 To lc - 6
    Text = g_arr(j, 1).Value
    g_arr(j, 1).Value = Mid(Text, InStr(Text, " ") + 1)
Next j
MsgBox ("Done!")
End Sub