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