# 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 extract text in brackets

## 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