remove numbers from each cell and keep the alphabets

my data is in sheet1
remove numbers from each cell and keep the alphabets
my data contains numbers and alphabets in most of cells(A2 in one cell,A2001in one cell,B5 in one cell,B4000in one cell)
what i want is to remove numbers from all cells and keep the alphabets
go to each cell remove numbers from all the cells and keep the alphabets
A,B,C.BI,BBB,etc remove numbers
i want to do this by vba
Avinash SinghAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi Avinash,
You may copy paste below code and run macro RemoveNumbers or you can insert one column next to your data column and type =RemNumb(A2) & drag down:
Sub RemoveNumbers()
Dim Ws As Worksheet
Dim LRow As Long
Dim Rng As Range, c As Range
Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set Rng = Ws.Range("A2:A" & LRow)
Application.ScreenUpdating = False
For Each c In Rng
    c.Value = RemNumb(c.Value)
Next c
Application.ScreenUpdating = True
End Sub
Function RemNumb(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemNumb = .Replace(Txt, "")
End With
End Function

Open in new window

Assuming your data starts from A2, change the range accordingly.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Select the cells to work with then run this macro

Option Explicit

Sub RemoveAllNumbers()

    Dim rCl As Range
    Dim iX As Integer
    Dim sTemp As String

    If Not TypeOf Selection Is Range Then Exit Sub

    On Error GoTo exit_proc

    With Application
        .ScreenUpdating = False    ''///prevent screen flickering, and save time
        .Calculation = xlCalculationManual    ''///save time calculating
        For Each rCl In Selection
            sTemp = vbNullString
            For iX = 1 To Len(rCl.Value)
                If Not IsNumeric(Mid(rCl.Value, iX, 1)) Then
                    sTemp = sTemp & Mid(rCl.Value, iX, 1)
                End If
            Next
            rCl.Value = sTemp
        Next
exit_proc:
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Open in new window

0
ShumsExcel & VBA ExpertCommented:
No Comments been added...
0
Avinash SinghAuthor Commented:
Thnx Roy and Shums Sir for giving ur precious time to this post plz close this thread its solved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.