Excel: extracting number with specific format out of text

Question

I need to extract a specific number out of multiple cells that contain text and numbers. The texts that I need to scan look more or less like that:

"Lorem ipsum dolor sit amet 5, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam 10%, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure 20% dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident 10.54%, sunt in culpa qui officia deserunt mollit anim id est laborum"

I need to extract the value in bold, which varies across texts, but is always a percentage, with two digits after the decimal point. There are other numbers and percentages in the text. To be specific: I need a way to extract only values with two digits after the decimal point out of text. Is there any way to achieve that with an excel function?

Will there always only be a single percentage with 2 digits after the decimal in your text (as shown in your provided sample)? — tigeravatar, Jan 23 '19 at 18:53
use find() to find the "%" sign and the space before that - the characters in between are the number... — Solar Mike, Jan 23 '19 at 18:55
@SolarMike but there are multiple instances of the `%` sign. — BigBen, Jan 23 '19 at 18:56
If there's always just a single instance of a decimal with 2 digits, this should work for you: `=MID(A3,SEARCH("??.??%",A3),6)` — tigeravatar, Jan 23 '19 at 19:00
@BigBen so expand and look for the decimal point as well... the comment was to give an idea of how to approach otherwise I would have considered an answer... — Solar Mike, Jan 23 '19 at 19:03
Some great info on using regular expressions in VBA to find specific patterns - https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Tim Williams, Jan 23 '19 at 19:36
are you limited to a maximum of 100.00%? or 3 digits to the left of the decimal or is it possible to have 1000000.00% or 1,000,000.00%? — Forward Ed, Jan 23 '19 at 19:49
The number I am looking for is always the only percentage with digits after decimal point. Usually two, sometimes more. It would be great if the formula could find any number with digits after decimal point, but at this stage finding everything with two will already be a huge improvement. I cannot provide any feedback right now, because I don't have access to the data. — borninthenorth, Jan 23 '19 at 19:58
@tigeravatar The formula you have provided works well, however after testing it out on my actual dataset, I found out that sometimes the number is not the only decimal value in the text. Nevertheless, it is always the *last* decimal value in the text. Is there a function that would use this characteristics? — borninthenorth, Jan 24 '19 at 09:23

score 0 · Answer 1 · answered Jan 24 '19 at 13:32

With normal Excel, I have no idea. With VBA, you can use an UDF to do this.

This code will get always the last % in text. It will get all digits. And it will return the % value as string. If you need it as number, then the UDF needs to be modified.

Public Function EXTRACT_LAST_PERCENT(ByVal vThisCell As Range) As String

If vThisCell.Count <> 1 Then
    EXTRACT_LAST_PERCENT = "This UDF only works with 1 cell"
    Exit Function
End If


Dim STR As String

STR = vThisCell.Value

Dim MyValues As Variant

MyValues = Split(STR, "%")

Dim i As Byte

'% target will be always the last, according to OP
'so it will be in Ubound-1 always
'we split again using blanks as delimiter

STR = MyValues(UBound(MyValues) - 1)

Erase MyValues

MyValues = Split(STR, " ")

'now % target is in Ubound


EXTRACT_LAST_PERCENT = MyValues(UBound(MyValues))

Erase MyValues

End Function

This is what I get:

Hope you can adapt this to your needs.

Excel: extracting number with specific format out of text

1 Answers1