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
```

Learn Excel Macro How to create User Defined Function in ExcelMay 4, 2014 at 8:59 pm[…] 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 […]

ShagunNovember 4, 2016 at 3:00 pmThanks. Very Helpful !

How to create User Defined Functions in Excel - Welcome to LearnExcelMacro.comSeptember 5, 2017 at 10:17 am[…] UDF to Extract Numbers from a cell […]