Excel Access VBA extract percentage from text

This Excel / Access tutorial explains how to extract percentage from text.

Access Excel VBA extract number from text or extract alphabet

Excel Extract Time from Date Time or Extract Date

Access Excel extract file name from file path

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

Text Formula Result
AA 5% dd =wExtractPercent(A2) 0.05
AAA   6.55% dd =wExtractPercent(A3) 0.0655
AAAA 17% =wExtractPercent(A4) 0.17
8% dd =wExtractPercent(A5) 0.08
9% =wExtractPercent(A6) 0.09

Outbound References

http://www.mrexcel.com/forum/excel-questions/515736-extract-percentage-cells.html

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *