Delete all numbers at the end of the cells

[Gamermatt]

Hello,

I need a formula or macro that will delete all the numbers that are at the end of the cells in column A. It should delete all numbers, starting from the end of the cell, until it reaches text.

Example:
(before) Lexus IS300 TA Style Carbon Fiber Rear Lip 00 01 02
(after) Lexus IS300 TA Style Carbon Fiber Rear Lip

Thanks,

Matt

 

[Scott Huish]

Function TrimRightNumbers(r As String) As String
Dim s As String, t, x As Long
s = StrReverse(r)
t = Split(s)
For x = LBound(t) To UBound(t)
    If Not IsNumeric(t(x)) Then Exit For
Next x
For x = LBound(t) To LBound(t) + (x - 1)
    t(x) = ""
Next x
TrimRightNumbers = Trim(StrReverse(Join(t)))
End Function

Excel Workbook
	A	B
1	Lexus IS300 TA Style Carbon Fiber Rear Lip 00 01 02	Lexus IS300 TA Style Carbon Fiber Rear Lip
2	Ford 500 GT Style 07 05	Ford 500 GT Style
Sheet1

 

[Peter_SSs]

Would it be possible that data could be like the following? (The number at the end is not separated from the text by a space). If so, what is the required result?
Lexus IS300 TA Style Carbon Fiber Rear Lip54

Would it be possible that the original text consists solely of numbers and/or spaces? If so, what is the expected result? Examples
23456
23 45 67

In any case, here is another UDF to consider.

<font face=Courier New><br><SPAN style="color:#00007F">Function</SPAN> TrimEndNumbers(s) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Static</SPAN> RegEx <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br><br>    <SPAN style="color:#00007F">If</SPAN> RegEx <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> RegEx = CreateObject("VBScript.RegExp")<br>        RegEx.Pattern = "([0-9 ]+$)"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    s = s & 0<br>    TrimEndNumbers = RegEx.Replace(s, "")<br>End <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Excel Workbook
	A	B
1	Lexus IS300 TA Style Carbon Fiber Rear Lip 00 01 02	Lexus IS300 TA Style Carbon Fiber Rear Lip
2	Ford 500 GT Style 07 05	Ford 500 GT Style
3	234 234
4	ABC	ABC
5	GM21	GM
Remove end numbers

 

[Biz]

Would it be possible that data could be like the following? (The number at the end is not separated from the text by a space). If so, what is the required result?
Lexus IS300 TA Style Carbon Fiber Rear Lip54

Would it be possible that the original text consists solely of numbers and/or spaces? If so, what is the expected result? Examples
23456
23 45 67

In any case, here is another UDF to consider.


Function TrimEndNumbers(s) As String
Static RegEx As Object

If RegEx Is Nothing Then
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "([0-9 ]+$)"
End If
s = s & 0
TrimEndNumbers = RegEx.Replace(s, "")
End Function

Excel Workbook
	A	B
1	Lexus IS300 TA Style Carbon Fiber Rear Lip 00 01 02	Lexus IS300 TA Style Carbon Fiber Rear Lip
2	Ford 500 GT Style 07 05	Ford 500 GT Style
3	234 234
4	ABC	ABC
5	GM21	GM
Remove end numbers

Nice VBScript.RegExp mate.

Biz

 

[Gamermatt]

Thanks guys, both solutions work! you rock :D