Formula for removing numbers from a cell

[MARK858]

Hi there, hopefully an easy question for someone out there. Does anyone know a formula to remove the numbers from a cell with the layout as below;

AB19S02 becomes ABS
BG36Z07 becomes BGZ
GH68L75 becomes GHL

and so on....
The cells are aligned to the right if that is any help
Cheers in advance for any suggestions.

 

[mikerickson]

If they are always of the form
< letter >< letter >< digit >< digit >< letter >< digit >< digit >

you could use the formula =LEFT(A1,2)&MID(A1, 5,1)

 

[RSXchin]

select whatever cells you want to get rid of the numbers and then start this macro. I would suggest making it a hotkey. i.e. ctrl+d or ctrl+m

Sub GetRidOfNumbers()
    For i = 1 To 9
        selection.Replace what:=i, replacement:="", lookat:= _
        xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
        ReplaceFormat:=False
    Next i
End Function

 

[iknowu99]

replace should work if you trying to get rid of numbers and you dont have to use vba to do it....but if you do don't forget zer0

use mikerickson method if it's first two letters and fifth

good luck

 

[RSXchin]

oh noez!!! I forgot zero

 

[Scott Huish]

Without a loop:

Function OnlyLetters(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z]"
    .IgnoreCase = True
    .Global = True
    OnlyLetters = .Replace(r, "")
End With
End Function

 

[MARK858]

Thanks to both of you for your help and speedy replies. They both work perfectly.
Rsxchin, I had to make a couple of adaptations to your code
A) changing

For i = 1 To 9 to For i = 0 To 9

B) Changing

End Function to End Sub

 

[RSXchin]

I'm still pretty new. I'll get your next problem

 

[MARK858]

Hotpepper,thanks for your suggestion I am getting it to work on a single cell well enough but having a bit of trouble getting it to work on a range but I'll have a proper play when I have a bit more time later in the week.

Rsxchin, your solution was fine it just needed minor tweaking

 

[Scott Huish]

Sub test()
Dim c As Range
For Each c In Range("A1:A1000")
    c = OnlyLetters(c.Text)
Next
End Sub

Function OnlyLetters(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z]"
    .IgnoreCase = True
    .Global = True
    OnlyLetters = .Replace(r, "")
End With
End Function