Formula for removing numbers from a cell

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,785
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

Well-known Member
Joined
Oct 23, 2010
Messages
758
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

Code:
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

Well-known Member
Joined
Dec 26, 2004
Messages
1,158
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

Well-known Member
Joined
Oct 23, 2010
Messages
758

ADVERTISEMENT

oh noez!!! I forgot zero :(
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Without a loop:

Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,436
Messages
5,528,749
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top