REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

VBA Remove Alpha Special characters from Range helps to remove unwanted characters and numbers from a selected Range . For example, user has to enter the non alphanumeric characters in a Cell or range. We need to do data validation whether user enters non alphanumeric or not.If user enters also, we need remove those alpha characters from cells using excel VBA.

VBA Remove Alpha Special characters from Range – Solution(s):

First of all we need to check whether entered data in a cell is alpha characters or not. If it is not with using following code we can remove those alpha characters from cell. Following are the two different methods.

Remove Alpha Special Characters using Excel VBA – Example Cases:

Here are the examples to show you how to remove the alpha and special characters from data. Following are the two different methods, the first one is a procedure to remove alpha and special characters from a specific range. And the second one is the user defined function to remove the alpha and special characters from Cells.

Remove Alpha Special characters – Using Procedure

Following is the example to Remove Alpha Special characters from cells in a selected Range using Excel VBA.

Code:
'Remove All Alpha and Special characters from cell or Range using VBA code
Sub Remove_AlphaCharacters_From_Cell_Or_Range()
    
    'Variable Declaration
    Dim iCnt As Integer
    Dim IpData As Range, DataRange As Range
    Dim sData As String, sTmp As String
    
    'Create Object for Selected Range
    On Error Resume Next
    Set DataRange = Sheets("Sheet1").Range("A2:A10")
    
    If DataRange.Count < 1 Then
        MsgBox ("Please select range which contains data in cells"), vbInformation
        Exit Sub
    End If
    
    'Runnning for loop to check all available cells
    For Each IpData In DataRange
        sTmp = ""
        For iCnt = 1 To Len(IpData.Value)
            If Mid(IpData.Value, iCnt, 1) Like "
[0-9]" Then sData = Mid(IpData.Value, iCnt, 1) Else sData = "" End If sTmp = sTmp & sData Next iCnt 'Reassign Final Numeric characters IpData.Value = sTmp Next IpData End Sub
Output:

Remove Alpha Special characters

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. Goto Sheet1 and Enter some data in Range(“A2:A10”) with AlphaNumeric and Special Characters
  6. Press F5 to remove Alpha and Special Characters from the data
  7. Now you should see only numeric data at A2:A10
Remove Alpha Special characters – Using Function

Following is the one more example to Remove Alpha Special characters from cells in a selected Range using user defined function with Excel VBA.

Code:
'Remove All Alpha and Special characters from cell
Function Remove_AlphaSpecialChar(DataCell As Range) As String
    
    'Variable Declaration
    Dim iCnt As Integer
    Dim IpData As Range
    Dim sData As String, sTmp As String
             
    If DataCell.Count <> 1 Then
        MsgBox ("Please select Single Cell"), vbInformation
        Exit Function
    End If
    
    'Runnning for loop to check all available Characters in a cell
    For iCnt = 1 To Len(DataCell.Text)
        If Mid(DataCell.Text, iCnt, 1) Like "[0-9]" Then
            sData = sData & Mid(DataCell.Text, iCnt, 1)
        End If
    Next iCnt
    
    Remove_AlphaSpecialChar = sData
End Function
Output:

Here is the screenshot for showing User defined function to remove Alpha and Special Characters from the cells.
Remove Alpha and Special Characters-Function

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above function and Paste in the code window
  5. Goto Sheet1 and Enter some data in Range(“A2:A10”) with AlphaNumeric and Special Characters
  6. In Column B, you can enter the formula (=Remove_AlphaSpecialChar(A2)) to remove the Alpha and Special Characters
  7. Now you should see only numeric data at B2, You can drag the formula upto B10.

Download Example File:

Please find the Example File to download, you can explore the code to see how to remove the alpha and special characters from a data using Excel VBA.
Download Now: AnalysisTabs- Remove all Alpha Special Characters

Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

One Comment

  1. Dhanuj April 29, 2019 at 2:29 AM

    Code given above is excellent.
    Can you provide me code for removing & replacing special character(according to my replaced words) from defined column

Leave A Comment