Remove certain characters from end of string

In A2-A6, you deleted all alpha chars after the last digit. A7, you deleted only 3 of 5, A8, 2 of 4, so what's the rule? Do you have a list of suffixes?

Do you have a list of core codes (without these suffixes)?

Excel needs something to use to establish a pattern. Your sample dataset has only two patterns, and there is nothing there that can readily be used to tell Excel what to do.

OK - so the only way you will be able to achieve this is to have a dynamic list of suffixes that can be added to. You cannot do it without a reference array. Do you understand this?

This means that you will need to work with a table containing the suffixes that can be referenced.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Should work as long as those cells in row 9 and 10 are not blank.

This is why I suggested that you need something that will adjust dynamically like a table.

In the attached you will find that I've converted your lookup list to a table - this will now adjust when you add new codes to it, and the formula will adapt automatically, too.

Just posting again in case you missed the attachment I added to my last post.

I have also spent some time with your duplicate thread, so just posting my solution in this thread.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Function RemoveSuffix(v As Variant, rList As Range) As String
Dim s As Variant

For Each s In rList.Cells.Value
    If UCase(s) = UCase(Right(v, Len(s))) Then
        RemoveSuffix = Left(v, Len(v) - Len(s))
        Exit Function
    End If
Next s

If RemoveSuffix = "" Then
    RemoveSuffix = v
End If

End Function

In B2 Cell

=RemoveSuffix(A2,Table1[Suffixes])

Drag the B2 cell formula down.