This Excel / Access tutorial explains how to extract percentage from text.
Excel Access VBA extract percentage from text
Recently I got a report that contains percentage but it is mixed with other text. For example,
Salary increased by 10%
We are unable to do any calculation unless we extract the 10% or 10 out from the text.
There are different ways to extract the number out from the text but I try to do it in custom Function because it can also be used in Access.
VBA Function – extract percentage from text
Public Function wExtractPercent(sInput) As Double If IsNumeric(sInput) Then wExtractPercent = sInput Else end_position = InStr(sInput, "%") For i = end_position To 1 Step -1 If Mid(sInput, i, 1) = " " Then start_position = i Exit For End If Next If start_position = 0 Then wExtractPercent = Left(sInput, end_position - 1) / 100 Else wExtractPercent = Mid(sInput, start_position, end_position - start_position) / 100 End If End If End Function
Explanation of VBA Function – extract percentage from text
First of all, the Function checks if the argument is a Text or a Number. If it is a Number, return the argument as is because nothing needs to be extracted.
If the argument is a Text, locate the position of % within the text using Instr Function, then loop backward from that position to 1 until a space is found, because I assume a space is separating the percentage. Finally we can extract the substring from the space position up to the % position.
If no space can be found (i=0), then we know the percentage starts from the first position, we just need to use Left Function to get the percentage.
Instead of extracting a number with % in the suffix, I extract the percentage in decimal value because % is just a matter of formatting and the underlying value is always a decimal.
Below are some examples of the Function
|AA 5% dd||=wExtractPercent(A2)||0.05|
|AAA 6.55% dd||=wExtractPercent(A3)||0.0655|