Access Excel VBA extract number from text or extract alphabet

This tutorial shows several Access Excel VBA custom functions to extract number from text, extract alphabet from text, extract symbols from text

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 Mid(var, i, 1) >= 0 And 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 Mid(var, i, 1) >= 0 And 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

Access Excel VBA extract number from text or extract alphabet

!	+	?	}
“	,	@	~
#	-	[
$	.	\
%	/	]
&	:	^
‘	;	_
(	<	`
)	=	{
*	>	|