Remove Alpha Special characters from Range using Excel VBA

Home/Excel VBA/Remove Alpha Special characters from Range using Excel VBA

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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:

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.

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: mongopono.ru- Remove all Alpha Special Characters

LIMITED TIME OFFER
By |September 29th, 2013|Excel VBA|1 Comment

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

One Comment

  1. Jhon December 4, 2013 at 9:44 AM - Reply

    This blog is great I love reading your VBA tutorials. Keep up the good work! Thanks- Jhon.

Leave A Comment


Related pages


excel project plan ganttinsert button vbausing arrays in excelascending order and descending order worksheetexcel hyperlink to cellcolumn index number excelactivex vbalock excel sheet for editingvba programming tutorialcombine multiple excel sheets into one sheetcreate vba exceloffset formula excelvba code to read excel filedelete empty rows excelhow to delete hyperlink in excelinterview questions on sas macroshow to change the width of a column in excelvlook up formulaexcel vba active workbookexcel spreadsheet cost analysisformula filter excelproject timelines templatesbyval vbahlookup example excel 2010hyperlink excel vbavb6 message boxfind password excel protected sheetlessons learned template excelworksheetfunction.vlookup vbavba range endhow to increase the column width in excelexplain vlookup in excel with examplecost analysis template excelhow to use hlookup in excel 2007 with examplevba cells rangevba cell addressvlookup using vbaexcel pivot table macrodynamic excel chartsmacro excel definitionexcel formula sumifsvb listboxcount cells in a range vbahow to see developer tab in excel 2010vbyesnocanceleffort estimation templateadvanced sorting in excelexcel vba range cleartcl commands in sql server with exampleshow to create excel dashboard reportsinterview question answer for fresherdcl commandvba save excel fileexcel vba controlsvba stepexcel hyperlink to cellexcel how to delete duplicatesusing excel macrosexcel 2013 slicer tutorialvlookup using two criteriavba update sqlvba meaningproject charter template excelhow to see developer tab in excel 2010how to unhide columns in excelascending order on excelpastespecial vbavba calling a subvba torrentvba input message boxdata control language in sql with examplevba copy fileshow to delete worksheet in excelwhat is a stock chart in excelhow to unprotect excel workbook 2013excel vba delete columnpowerpivot excel 2010 tutorial