Extracting text from a string with variable length

[TobiasL]

Hey I got a long String like this "[...] increase of x.xx% [...]".

I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.

I tried this formula:

=MID(G14,SEARCH("%",G14)-5,5)

but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.

Thanks for any help!

Tobi

 

[Trebor76]

Hi there,

If the percentage always follows the text "increase of ", the following should do the trick:

=MID(G14,SEARCH("increase of ",G14)+12,LEN(G14)-SEARCH("increase of ",G14)+12)

HTH

Robert

 

[jindon]

UDF
1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code
3) hit Alt + F11 again
Use in cell like
=myPercent(A1)

Function myPercent(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+(\.\d+)?%"
    myPercent = .execute(txt)(0)
End With
End Function

 

[TobiasL]

UDF
1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code
3) hit Alt + F11 again
Use in cell like
=myPercent(A1)

Function myPercent(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+(\.\d+)?%"
    myPercent = .execute(txt)(0)
End With
End Function

That works great thanks! Only one thing. Is it possible to get rid of the percentage and convert this string into a number? thanks!

 

[jindon]

try

Function myPercent(ByVal txt As String) As Double
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+(\.\d+)?)%"
    myPercent = .execute(txt)(0).SubMastches(0)
End With
End Function

 

[mfexcel]

UDF
1) Hit Alt + F11
2) go to [Insert] - [Module] then paste the code
3) hit Alt + F11 again
Use in cell like
=myPercent(A1)

Function myPercent(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\d+(\.\d+)?%"
    myPercent = .execute(txt)(0)
End With
End Function

wondering... can it be done by formula? espically if the number could be ended with any characters including space?

 

[TobiasL]

try

Function myPercent(ByVal txt As String) As Double
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+(\.\d+)?)%"
    myPercent = .execute(txt)(0).SubMastches(0)
End With
End Function

awesome thanks so much! is it also possible to catch several percentage values with this code?

 

[jindon]

How do you want the result ?
This will put all numbers in one cell
=myPercent(A1,",")

Function myPercent(ByVal txt As String, ByVal myJoin As String) As String
Dim m As Object
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+(\.\d+)?)%"
    .Global = True
    For Each m In .execute(txt)
        myPercent = myPercent & IIf(myPercent = "","", myJoin) & _
            m.submatches(0)
    Next
End With
End Function

This will cell by cell in horizontally
=myPercent($A1,Column(A1))
then copy to the right

if you want them in vertically
=myPercent(A$1,Row(A1))
then copy down

Function myPercent(ByVal txt As String, ByVal ref As Long)
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+(\.\d+)?)%"
    myPercent = .execute(txt)(ref - 1).SubMastches(0)
End With
End Function

 

[TobiasL]

How do you want the result ?
This will put all numbers in one cell
=myPercent(A1,",")

Function myPercent(ByVal txt As String, ByVal myJoin As String) As String
Dim m As Object
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+(\.\d+)?)%"
    .Global = True
    For Each m In .execute(txt)
        myPercent = myPercent & IIf(myPercent = "","", myJoin) & _
            m.submatches(0)
    Next
End With
End Function

This will cell by cell in horizontally
=myPercent($A1,Column(A1))
then copy to the right

if you want them in vertically
=myPercent(A$1,Row(A1))
then copy down

Function myPercent(ByVal txt As String, ByVal ref As Long)
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+(\.\d+)?)%"
    myPercent = .execute(txt)(ref - 1).SubMastches(0)
End With
End Function

Awesome again! domo arregato!

 

[jindon]

wondering... can it be done by formula? espically if the number could be ended with any characters including space?

=NumberBeforeChar(A1,"-")

Function NumberBeforeChar(ByVal txt As String, ByVal myChr As String) As Double
Select Case myChr
    Case "[", "]", "(", ")", "{", "}", "+", "*", "?", "$", "^", "\", ".", "|"
    myChr = "\" & myChr
End Select
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+(\.\d+)?)" & myChr
    NumberBeforeChar = .execute(txt)(0).SubMastches(0)
End With
End Function