This tutorial shows several Access Excel VBA custom functions to extract number from text, extract alphabet from text, extract symbols from text
Excel Access VBA extract percentage from text
Excel Extract Time from Date Time or Extract Date
Access Excel extract file name from file path
Access Excel VBA extract number from text or extract alphabet from text
In this article, I will show a list of custom Functions to perform the following tasks.
– Check if a text contains any alphabet, if a text contains only alphabets, extract alphabet from text
– Check if a text contains any symbols, extract symbols from text
– Check if a text contains any number, if a text contains only number, extract number from text, extract number from text and then sum only the number part in a Range (sum text).
These functions are useful in data verification. For example, you may want to check if an employee ID contains only number. You may also want to check if employee name does not contain symbols. Some function are based on ASC Function for checking of alphabets and symbols.
1.1 Check if a text contains any alphabet
The below function checks if a text contains alphabet, regardless of capital letter or small letter. This function returns TRUE if an alphabet is found, otherwise returns FALSE.
Public Function wCheckAlphabet(var)
For i = 1 To Len(var)
If Asc(Mid(UCase(var), i, 1)) >= 65 And Asc(Mid(UCase(var), i, 1)) <= 90 Then
wCheckAlphabet = True
Exit Function
Else
wCheckAlphabet = False
End If
Next i
End Function
1.2 Check if a text contains ONLY alphabets
The below function checks if a text contains only alphabets, regardless of capital letter or small letter. This function returns TRUE if only alphabets are found in the text, otherwise returns FALSE.
Public Function wCheckOnlyAlphabet(var)
For i = 1 To Len(var)
If Asc(Mid(UCase(var), i, 1)) >= 65 And Asc(Mid(UCase(var), i, 1)) <= 90 Then
wCheckOnlyAlphabet = True
Else
wCheckOnlyAlphabet = False
Exit Function
End If
Next i
End Function
1.3 Extract alphabet from Text
Public Function wExtractAlphabet(var)
For i = 1 To Len(var)
If Asc(Mid(UCase(var), i, 1)) >= 65 And Asc(Mid(UCase(var), i, 1)) <= 90 Then
result = result & Mid(var, i, 1)
End If
Next i
wExtractAlphabet = result
End Function
2.1 Check if a text contains any symbols
The below function checks if a text contains the below symbols, return TRUE if any one of the below is found.
| ! | + | ? | } |
| “ | , | @ | ~ |
| # | – | [ | |
| $ | . | \ | |
| % | / | ] | |
| & | : | ^ | |
| ‘ | ; | _ | |
| ( | < | ` | |
| ) | = | { | |
| * | > | | | |
Public Function wCheckSymbol(var)
For i = 1 To Len(var)
If (Asc(Mid(var, i, 1)) >= 33 And Asc(Mid(var, i, 1)) <= 47) Or _
(Asc(Mid(var, i, 1)) >= 58 And Asc(Mid(var, i, 1)) <= 64) Or _
(Asc(Mid(var, i, 1)) >= 91 And Asc(Mid(var, i, 1)) <= 96) Or _
(Asc(Mid(var, i, 1)) >= 123 And Asc(Mid(var, i, 1)) <= 126) Then
wCheckSymbol = True
Exit Function
Else
wCheckSymbol = False
End If
Next i
End Function
2.2 Extract symbols from text
Public Function wExtractSymbol(var)
For i = 1 To Len(var)
If (Asc(Mid(var, i, 1)) >= 33 And Asc(Mid(var, i, 1)) <= 47) Or _
(Asc(Mid(var, i, 1)) >= 58 And Asc(Mid(var, i, 1)) <= 64) Or _
(Asc(Mid(var, i, 1)) >= 91 And Asc(Mid(var, i, 1)) <= 96) Or _
(Asc(Mid(var, i, 1)) >= 123 And Asc(Mid(var, i, 1)) <= 126) Then
result = result & Mid(var, i, 1)
End If
Next i
wExtractSymbol = result
End Function
3.1 Check if a text contains any number
The below function checks if a text contains number. This function returns TRUE if a number is found, otherwise returns FALSE.
Public Function wCheckNumber(var)
For i = 1 To Len(var)
If Asc(Mid(var, i, 1)) >= 0 And Asc(Mid(var, i, 1)) <= 9 Then
wCheckNumber = True
Exit Function
Else
wCheckNumber = False
End If
Next i
End Function
3.2 Check if a text contains ONLY number
The below function checks if a text contains only number. This function returns TRUE if only number is found, otherwise returns FALSE.
Public Function wCheckOnlyNumber(var)
For i = 1 To Len(var)
If Asc(Mid(var, i, 1)) >= 0 And Asc(Mid(var, i, 1)) <= 9 Then
wCheckOnlyNumber = True
Else
wCheckOnlyNumber = False
Exit Function
End If
Next i
End Function
3.3 Extract number from text
The below Function extracts number from a text. For example, =wExtractNumber(“#123”) would return 123
Public Function wExtractNumber(sinput) As Double
For i = 1 To Len(sinput)
If IsNumeric(Mid(sinput, i, 1)) Then
result = result & Mid(sinput, i, 1)
End If
Next i
wExtractNumber = result
End Function
3.4 Sum number from text in a Range (sum text) – For Excel only
The below Function is used like the regular SUM Function, except that it sums only the number part of the text. You have to also copy the above Function ExtractNumber in order for the below Function to work.
Public Function wSumExtractNumber(sinput As Range) As Double
For Each Rng In sinput
If IsNumeric(ExtractNumber(Rng.Value)) Then
result = result + ExtractNumber(Rng.Value)
End If
Next Rng
wSumExtractNumber = result
End Function
Outbound References
http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-i-create-a-number-format-for-a-cell-with/b307979f-9efa-4f14-89be-bb74d5223d66
