removing prefix from part numbers

[22strider]

Hello Firends

I have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?

Following are some of the examples of prefixes:
AB
GD
KR
BCD
FP-
TJ-
W

Thanks for your time
Rajesh

 

[Quasi]

Hi!

Try this VBA Function

Option Explicit

Function NoPrefixes(Text As String) As String

    Dim PreList As Variant
    Dim PreLen As Integer
    Dim TextLen As Integer
    Dim x As Byte
    
    Application.Volatile True

    PreList = Array("AB", "GD", "KR", "BCD", "FP-", "TJ-", "W") 'You must complete this list
    TextLen = Trim(Len(Text))
    NoPrefixes = Text

    For x = 0 To UBound(PreList)
        PreLen = Len(PreList(x))
            If InStr(1, UCase(Left(Text, PreLen)), UCase(PreList(x))) Then
                NoPrefixes = Right(Text, TextLen - PreLen)
            End If
    Next x

End Function