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