Remove numbers from end of string

[FryGirl]

I would like to remove all of the trailing numbers, but can it be done in the same column. I've seen some ways to do it in another column, but how about the same column.

Before	After
House Gifts 001	House Gifts
House Gifts 002	House Gifts
House Gifts 003	House Gifts
House Gifts 004	House Gifts
Car Wash 35	Car Wash
Car Wash 36	Car Wash
Car Wash 37	Car Wash
Hand Washing Kit 12	Hand Washing Kit
Hand Washing Kit 13	Hand Washing Kit
Hand Washing Kit 14	Hand Washing Kit
Hand Washing Kit 1	Hand Washing Kit
Hand Washing Kit 2	Hand Washing Kit

 

[Joe4]

To do it in the same column will most likely require VBA.
Are you open to a VBA solution?

 

[FryGirl]

Yes please

 

[kweaver]

How about:

Sub RemoveNum()
Dim lr, i, p As Long, txt As String

lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
 txt = Cells(i, "A") ' text string in cell
 For p = 0 To 9
   txt = Replace(txt, p, "")
 Next p
 Cells(i, "A") = Trim(txt)
Next i
  
End Sub

 

[Joe4]

Try this, which only requires one loop (you want to limit loops as much as possible, as they are generally slow/inefficient):

Sub MyFixData()

    Dim lr As Long
    Dim r As Long
    Dim n As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows
    For r = lr To 1 Step -1
'       Find location of last space in entry
        n = InStrRev(Trim(Cells(r, "A")), " ")
'       Extract last number from string
        If n > 1 Then Cells(r, "A") = Left(Cells(r, "A"), n - 1)
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

 

[DanteAmor]

With out loop.
Try this:

Sub deletenumbers()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},LEFT(" & .Address & ",LEN(" & .Address & ")-LEN(TRIM(RIGHT(SUBSTITUTE(" & .Address & ", "" "",REPT("" "",99)),99)))))")
  End With
End Sub

 

[Joe4]

With out loop.
Try this:

Sub deletenumbers()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},LEFT(" & .Address & ",LEN(" & .Address & ")-LEN(TRIM(RIGHT(SUBSTITUTE(" & .Address & ", "" "",REPT("" "",99)),99)))))")
  End With
End Sub

Nice Dante!

I figured that there had to be a way without loops, but couldn't come up with it.
I haven't used "EVALUATE" that much.

 

[DanteAmor]

Thanks Joe for your comments.

In some versions of excel the first IF is not necessary. But in my version of excel I always have to put it.

.Value = Evaluate("=LEFT(" & .Address & ",LEN(" & .Address & ")-LEN(TRIM(RIGHT(SUBSTITUTE(" & .Address & ", "" "",REPT("" "",99)),99))))")

It can also be simplified to something like this, so as not to cut through .address every time it's required:

Sub deletenumbers()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("=IF({1},LEFT(@,LEN(@)-LEN(TRIM(RIGHT(SUBSTITUTE(@, "" "",REPT("" "",99)),99)))))", "@", "" & .Address & ""))
  End With
End Sub

 

[FryGirl]

Thank you to all. These solutions worked great. I too like the Evaluate method, but it always confuses me.