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]

Here a UDF with regular expression.

Alt+F11 to open the VBA editor. Insert a new module and paste this code. Afterwards you can use the function like --> =jec(A1)

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

sorteren.xlsx
	A	B
1	999-TRANSFER: Y.REF.83237 O.REF.USERINT-MSCI SWFPRCXXXXXPRC TRANSFER OF USD 3,150.00 IN FAVOUR OF ENTITY1 MGT FEES Q2 2023 bq	MGT FEES Q2 2023
2	999-TRANSFER: Y.REF. O.REF.USERINT-HNOL SWFPRC039XXXXXPRC TRANSFER OF USD 24.63 IN FAVOUR OF ENTITY2 VAT FEES 2022 B6AT A FX RATE OF .92828962 FX PAYMENT EQUIVALENT TO EUR 22.86	VAT FEES 2022
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=jec(A1)

 

[Bering]

Here a UDF with regular expression.

Alt+F11 to open the VBA editor. Insert a new module and paste this code. Afterwards you can use the formula like --> =jec(A1)

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

sorteren.xlsx
	A	B
1	999-TRANSFER: Y.REF.83237 O.REF.USERINT-MSCI SWFPRCXXXXXPRC TRANSFER OF USD 3,150.00 IN FAVOUR OF ENTITY1 MGT FEES Q2 2023 bq	MGT FEES Q2 2023
2	999-TRANSFER: Y.REF. O.REF.USERINT-HNOL SWFPRC039XXXXXPRC TRANSFER OF USD 24.63 IN FAVOUR OF ENTITY2 VAT FEES 2022 B6AT A FX RATE OF .92828962 FX PAYMENT EQUIVALENT TO EUR 22.86	VAT FEES 2022
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=jec(A1)

Thank you! I will test it later and let you know

 

[DanteAmor]

Try:

varios 07ene2024.xlsm
	T	U
1	999-TRANSFER: Y.REF.83237 O.REF.USERINT-MSCI SWFPRCXXXXXPRC TRANSFER OF USD 3,150.00 IN FAVOUR OF ENTITY1 MGT FEES Q2 2023	MGT FEES Q2 2023
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	VAT FEES 2022
Hoja5

Cell Formulas
Range	Formula
U1:U2	U1	=MID(T1,SEARCH("FEES",T1)-4,SEARCH(" ???? ",T1&" ",SEARCH("FEES",T1))-SEARCH("FEES",T1)+9)

 

[Bering]

Here a UDF with regular expression.

Alt+F11 to open the VBA editor. Insert a new module and paste this code. Afterwards you can use the function like --> =jec(A1)

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

sorteren.xlsx
	A	B
1	999-TRANSFER: Y.REF.83237 O.REF.USERINT-MSCI SWFPRCXXXXXPRC TRANSFER OF USD 3,150.00 IN FAVOUR OF ENTITY1 MGT FEES Q2 2023 bq	MGT FEES Q2 2023
2	999-TRANSFER: Y.REF. O.REF.USERINT-HNOL SWFPRC039XXXXXPRC TRANSFER OF USD 24.63 IN FAVOUR OF ENTITY2 VAT FEES 2022 B6AT A FX RATE OF .92828962 FX PAYMENT EQUIVALENT TO EUR 22.86	VAT FEES 2022
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=jec(A1)

WOW, this works like a charm, thank you very much!

 

[Bering]

Try:

varios 07ene2024.xlsm
	T	U
1	999-TRANSFER: Y.REF.83237 O.REF.USERINT-MSCI SWFPRCXXXXXPRC TRANSFER OF USD 3,150.00 IN FAVOUR OF ENTITY1 MGT FEES Q2 2023	MGT FEES Q2 2023
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	VAT FEES 2022
Hoja5

Cell Formulas
Range	Formula
U1:U2	U1	=MID(T1,SEARCH("FEES",T1)-4,SEARCH(" ???? ",T1&" ",SEARCH("FEES",T1))-SEARCH("FEES",T1)+9)

Thank you very much, it works perfectly if the fees type is of 3 characters.
So if I have Administration fees, the result is ion fees. My bad, this was not clear from my examples.

 

[JEC]

You're welcome! Cheers

 

[Bering]

WOW, this works like a charm, thank you very much!

Hello JEC,
I am truly fascinated by regex now.
Hope you don’t mind me asking some more help…
what if I wanted to be able to also extract “TAX” followed by the pattern ([Q0-9 ]+), in addition to the rest?
In this case I don’t anything preceding the word TAX. Thank you!!

 

[Peter_SSs]

Just a comment in relation to the marked solution.
It won't hurt but I don't see any (reason for)/(value in) the line .Global = True

 

[Bering]

Just a comment in relation to the marked solution.
It won't hurt but I don't see any (reason for)/(value in) the line .Global = True

Thanks, what would change if that was set as False?