Removing last two characters having alphabets

[Deepk]

Hi team,

I have list of entries in an excel column, see example below

AB203467A1
SC345745BB
KC983434T
HE98765P1
AS456382

I want to have a macro that convert these numbers to the following and paste in the next column

AB203467 (A1 removed)
SC345745 (BB removed)
KC983434 (T removed)
HE98765 (P1 removed)
AS456382 (nothing removed)

In short, I want to remove

last two characters if a number is followed by alphabet or both characters are alphabets
only last character if it is alphabet
nothing is removed if there is no alphabet in last two characters.

Please help me with this. Thanks in advance.

 

[Peter_SSs]

I have modified your code a bit but its showing an error

It looks like you may be trying to work one cell at a time, rather than processing all at once.
- Is that correct?
- If so, why?
- If I have mis-interpreted, can you explain why the original code was not suitable and what you are trying to achieve with the new code.

If it is simply that you want the code to work on your selected cells rather than column A and assuming that the selection is a single column/row/block of cells, then the change would be

Sub RemoveAtEndOfSelection()
  With Selection
    .Value = Evaluate(Replace("if(#="""","""",left(#,len(#)-2+if(isnumber(mid(#,len(#)-1,1)+0),1+isnumber(right(#,1)+0),0)))", "#", .Address))
  End With
End Sub