Loop formula to remove last letter of productcode if it is a letter

[Riaaz66]

Hi Guys,

In column A, I have a list of productcodes. Some of them end with a letter.
I need to have a loop formula where the last character of the productcode should be removed ONLY if it ends with a letter.
I do not know how to determine that the last character is a letter.

Productcode Change To
TRS11580 TRS11580
TRS8180B TRS8180
TRS11155G TRS11155
TRS11301C TRS11301
TRS9029C TRS9029
TRS11749 TRS11749
TRS11282 TRS11282
SWAP2589A SWAP2589
TRS10079 TRS10079
TRS2579B TRS2579B

Thanks in advance,

Riaaz66

[Bob Phillips]

[VBA]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row
For i = 2 To Lastrow

If Not IsNumeric(Right$(.Cells(i, "A").Value2, 1)) Then

.Cells(i, "A").Value2 = Left$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/VBA]

[Riaaz66]

Hi Xld,

Thank you very much. My problem is solved.
This thread can be marked as closed.

Thanks again.

[Riaaz66]

Oops,

I think I was to enthousiastic in my previous post. Really sorry for that.

My data starts in column B6 up to the end. If I run the provided macro, I get this error:
Run-time error '5':Invalid procedure call or argument. When I press the debug button, the following line is marked yellow:
.Cells(i, "A").Value2 = Left$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)

However, when I copy the data on column A1, the macro runs fine.
I want the macro to be able to run from a activecell till the last row of that column.

Can you please also expalin we the "a" and the "A" in the code. Does it refer to a letter or to a column?

Thanks in advance and kind regards,
Riaaz66

[macropod]

FWIW, a formula can strip off the letter and output the result in another cell. For example:
=IF(CODE(RIGHT(A2))>64,LEFT(A2,LEN(A2)-1),A2)

[Bob Phillips]

Oops,

I think I was to enthousiastic in my previous post. Really sorry for that.

My data starts in column B6 up to the end. If I run the provided macro, I get this error:
Run-time error '5':Invalid procedure call or argument. When I press the debug button, the following line is marked yellow:
.Cells(i, "A").Value2 = Left$(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - 1)

However, when I copy the data on column A1, the macro runs fine.
I want the macro to be able to run from a activecell till the last row of that column.

Can you please also expalin we the "a" and the "A" in the code. Does it refer to a letter or to a column?

Thanks in advance and kind regards,
Riaaz66

Just change it to

[vba]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 6 To Lastrow

If Not IsNumeric(Right$(.Cells(i, "B").Value2, 1)) Then

.Cells(i, "B").Value2 = Left$(.Cells(i, "B").Value2, Len(.Cells(i, "B").Value2) - 1)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]