Substring extraction based on condition

[Bering]

Hello,
I would need to find if any string in column T contains the word "FEES" and if so, extract the preceding word plus FEES in the adjacent column.

Example, description in T1:

999-TRANSFER: Y.REF.83237 O.REF.USERINT-MSCI SWFPRCXXXXXPRC TRANSFER OF USD 3,150.00 IN FAVOUR OF ENTITY1 MGT FEES Q2 2023

Result in cell U1:
MGT FEES
If "MGT FEES Q2 2023" was possible that would be even better.

NB, The string FEES can appear anywhere in the description and the length of it is variable.

Example 2:

999-TRANSFER: Y.REF. O.REF.USERINT-HNOL SWFPRC039XXXXXPRC TRANSFER OF USD 24.63 IN FAVOUR OF ENTITY2 VAT FEES 2022 AT A FX RATE OF .92828962 FX PAYMENT EQUIVALENT TO EUR 22.86

The expected result here should be:

VAT FEES
or
even better, VAT FEES 2022

Appreciate any suggestions, thank you!

 

[JEC]

Global = true means that it will match substring mulitple times
I have the habbit to write it automatically. But it is not necessary here.

 

[Peter_SSs]

I wouldn't change it to False, I would leave the whole line out as I don't think it contributes anything to the result. I don't think anything would change. Try it.

 

[Bering]

Global = true means that it will match all substrings.
I have the habbit to write it automatically. But it is not necessary here.

Gotcha thanks

 

[Bering]

I wouldn't change it to False, I would leave the whole line out as I don't think it contributes anything to the result. I don't think anything would change. Try it.

Thanks, clear

 

[JEC]

Could there be TAX and FEES in one string?

 

[Bering]

Could there be TAX and FEES in one string?

Nope, either one or the other

 

[JEC]

Well, try this. Without the global because you can only have one match.

Function jec(c As String) As String
 With CreateObject("vbscript.regexp")
   .Pattern = "([A-Z]+\sFEES|TAX)([Q0-9 ]+)"
   If .test(c) Then jec = Trim(.Execute(c)(0))
 End With
End Function

 

[Bering]

Well, try this. Without the global because you can only have one match.

Function jec(c As String) As String
 With CreateObject("vbscript.regexp")
   .Pattern = "([A-Z]+\sFEES|TAX)([Q0-9 ]+)"
   If .test(c) Then jec = Trim(.Execute(c)(0))
 End With
End Function

It works perfectly. What resources would you recommend to learn this? Thank you so much.

 

[JEC]

Great! I would start with this website

VBA Regex

This tutorial will demonstrate how to use Regex in VBA. What is Regex? Regex stands for regular expression. A regular expression is a pattern made up of a

www.automateexcel.com

 

[Bering]

Thank you!!