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

 

[TobiasL]

=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

hey thanks again for the script. The problem is that when I am pasting that function in 500 cells and more, that Excel gets really slow and recalculates these cells in short timestamps.

Any help for that?

thanks
Tobi

 

[ZVI]

Early binding will considerably speed up performance of the code proposed by Jindon.

Alternatively this one can be used as well:

<font face=Courier New>
' Returns number value before % char in Ref string
' =PC(A1) or =PC(A1,1) returns the 1st case (ocurrence) of percent value in A1
' =PC(A1,2) returns the 2nd case of percent value in A1, and so on
Function PC(Ref As String, Optional Num As Integer = 1)
  Dim s$, arr
  PC = ""
  arr = Split(Ref, "%")
  If Num > UBound(arr) + 1 Or Num < 1 Then Exit Function
  s = Trim$(arr(Num - 1))
  s = Mid$(s, InStrRev(s, " ") + 1)
  If IsNumeric(Mid(s, 1, 1)) Then PC = Val(s)
End Function</FONT>

Regards,
Vladimir

 

[ZVI]

To be more correct please replace this line of code:

If Num > UBound(arr) + 1 Or Num < 1 Then Exit Function

to:
If Num > UBound(arr) Or Num < 1 Then Exit Function

 

[TobiasL]

Early binding will considerably speed up performance of the code proposed by Jindon.

Alternatively this one can be used as well:

' Returns number value before % char in Ref string
' =PC(A1) or =PC(A1,1) returns the 1st case (ocurrence) of percent value in A1
' =PC(A1,2) returns the 2nd case of percent value in A1, and so on
Function PC(Ref As String, Optional Num As Integer = 1)
  Dim s$, arr
  PC = ""
  arr = Split(Ref, "%")
  If Num > UBound(arr) + 1 Or Num < 1 Then Exit Function
  s = Trim$(arr(Num - 1))
  s = Mid$(s, InStrRev(s, " ") + 1)
  If IsNumeric(Mid(s, 1, 1)) Then PC = Val(s)
End Function

Regards,
Vladimir

Hey vladimir, thanks for helping. Could you explain how I can implement early binding in Jindon's code?

Thanks

 

[ZVI]

Hi Tobi,

To set early binding do as follows:
1. In Excel go to VBE by pressing of Alt-F11
2. Choose menu: Tools-References
3. Find in a references list the item named as Microsoft VBScript Regular Expression 5.5 and set On its checkbox.
4. Replace this line of Jindon's code: With CreateObject("VBScript.RegExp")
by that one: With New RegExp

Regards,
Vladimir

 

[TobiasL]

Early binding will considerably speed up performance of the code proposed by Jindon.

Alternatively this one can be used as well:

' Returns number value before % char in Ref string
' =PC(A1) or =PC(A1,1) returns the 1st case (ocurrence) of percent value in A1
' =PC(A1,2) returns the 2nd case of percent value in A1, and so on
Function PC(Ref As String, Optional Num As Integer = 1)
  Dim s$, arr
  PC = ""
  arr = Split(Ref, "%")
  If Num > UBound(arr) + 1 Or Num < 1 Then Exit Function
  s = Trim$(arr(Num - 1))
  s = Mid$(s, InStrRev(s, " ") + 1)
  If IsNumeric(Mid(s, 1, 1)) Then PC = Val(s)
End Function

Regards,
Vladimir

Hey vladimir could you show me how I can modify your code so that I can extract different patterns. For example I have a string like this "At the time this buy order was entered, it was x7,115 shares from the priority bid"
I want to extract all figures which have an "x" in the beginning (7,115)

Thanks again!

 

[jindon]

Hey vladimir could you show me how I can modify your code so that I can extract different patterns. For example I have a string like this "At the time this buy order was entered, it was x7,115 shares from the priority bid"
I want to extract all figures which have an "x" in the beginning (7,115)

Thanks again!

TobiasL

Open new thread for this and it can be done by Formula without vba.

 

[mikerickson]

This might do it for you
=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")), FIND("%",A1&"%")-MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1234567890")))