Sometimes you require to Extract All Numbers and Characters from a mixed String. I have written a UDF (User Defined Function) to extract them.

  1. UDF Function to Extract Numbers from a String
  2. UDF Function to Extract Special Characters from a String
  3. 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:

Extract Number, Special Chars and Alphabets from a String

Extract Number, Special Chars and Alphabets from a String


Extract Number, Special Chars and Alphabets

Extract Number, Special Chars and Alphabets from a String


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))  90) _
			And ((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))  90) _
		And ((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)) = 97 And Asc(Mid(InputString, i, 1))