Excel UDF: To exctract All Numbers, Special Characters and Alphabets from a String
Sometimes you require to Extract All Numbers and Characters from a mixed String. I have written a UDF (User Defined Function) to extract them.

UDF Function to Extract Numbers from a String

UDF Function to Extract Special Characters from a String

UDF Function to Extract Alhpabets from a String
Add the below Code in any of your Regular Module of the Excel VBA Editor. Now go to your Excel Workbook and Type this formula. It will extract All the Special Characters, Numbers and Alphabets seperately as shown below:
Function ExtractNumber(Cell As Range) Dim ResultNum As Long Dim ResultSpecialChar, ResultAlpha As String Dim InputString As String InputString = Cell.Value For i = 1 To Len(InputString) If IsNumeric(Mid(InputString, i, 1)) = True Then Result = Result & Mid(InputString, i, 1) ElseIf (Asc(Mid(InputString, i, 1)) <= 65 Or Asc(Mid(InputString, i, 1)) > 90) _ And ((Asc(Mid(InputString, i, 1)) < 97 Or Asc(Mid(InputString, i, 1)) >= 122)) Then ResultSpecialChar = ResultSpecialChar & Mid(InputString, i, 1) Else ResultAlpha = ResultAlpha & Mid(InputString, i, 1) End If Next ExtractNumber = "Alphabets are: " & ResultAlpha & " ** Numbers are: " & Result & " ** Special Chars:" & ResultSpecialChar End Function
If you want only Numbers to be extracted from the String then Use the below code in Module
Function ExtractNumber(Cell As Range) Dim ResultNum As Long Dim InputString As String InputString = Cell.Value For i = 1 To Len(InputString) If IsNumeric(Mid(InputString, i, 1)) = True Then ResultNum = ResultNum & Mid(InputString, i, 1) End If Next ExtractNumber = ResultNum End Function
If you want only Special Characters to be extracted from the String then Use the below code in Module
Function ExtractSpecialChar(Cell As Range) Dim ResultSpecialChar As String Dim InputString As String InputString = Cell.Value For i = 1 To Len(InputString) If (Asc(Mid(InputString, i, 1)) <= 65 Or Asc(Mid(InputString, i, 1)) > 90) _ And ((Asc(Mid(InputString, i, 1)) < 97 Or Asc(Mid(InputString, i, 1)) >= 122) _ And IsNumeric(Mid(InputString, i, 1)) = False) Then ResultSpecialChar = ResultSpecialChar & Mid(InputString, i, 1) End If Next ExtractSpecialChar = ResultSpecialChar End Function
If you want only Alhabets to be extracted from the String then Use the below code in Module
Function ExtractAlphabets(Cell As Range) Dim ResultAlphabet As String Dim InputString As String InputString = Cell.Value For i = 1 To Len(InputString) If (Asc(Mid(InputString, i, 1)) >= 65 And Asc(Mid(InputString, i, 1)) <= 90) _ Or ((Asc(Mid(InputString, i, 1)) >= 97 And Asc(Mid(InputString, i, 1)) <= 122)) Then ResultAlphabet = ResultAlphabet & Mid(InputString, i, 1) End If Next ExtractAlphabets = ResultAlphabet End Function
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P

SUBSCRIBEAND GET A FREE!EBOOK FOR EXCEL VBA BEGINNERSDON'T MISS ANY NEW ARTICLE ! 
Get your FREE! EBook & Have Every New Article Delivered Straight To Your EmailBox 
[…] UDF – Is File Open? UDF to Convert Currency to Words UDF to Count Words in Cell UDF to Extract Numbers from a cell UDF to Extract Special Characters from a cell UDF to Extract Alphabets from a cell UDF to […]