Find and Remove Strings

February 05, 2002 - by Juan Pablo Gonzalez

Mike asks:

Is there any way I can simply remove certain characters from a text string? For example, I have a post code BN19 4GX and I want to remove all numeric charaters to leave BN GX.

This is easier using VBA.

Go to the VB Editor (Alt + F11), go to Insert, Module. Paste this code in there.

Function RemoveNumeric(Rng As String) As String
Dim Tmp As String
Dim i As Integer

    Tmp = Rng
    For i = 0 To 9
        Tmp = Application.Substitute(Tmp, i, "")
    Next i
    RemoveNumeric = Tmp
End Function

Now, back in Excel, if your initial text is in A1, put this formula where you want the result:

=RemoveNumeric(A1)

and you'll get 'BN GX'. The function removes all numeric characters, that is, {0,1,2,3,4,5,6,7,8,9}, but can be adapted to substitute something else.