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?
 
ShumsDistinguished Expert - 2017Commented:
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
 
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
 
ShumsDistinguished Expert - 2017Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.