BILL PAYMENT VIA FASTER PAYMENT TO ABC Ltd REFERENCE UK , MANDATE 333 BILL PAYMENT VIA FASTER PAYMENT TO ACME LLC REFERENCE UK , MANDATE 375

BILL PAYMENT VIA FASTER PAYMENT TO ABC Ltd REFERENCE UK , MANDATE 333 BILL PAYMENT VIA FASTER PAYMENT TO ACME LLC REFERENCE UK , MANDATE 375

Thank you very much for the solution.Try,
In B2, formula copied down :
=MID(A2,FIND("TO",A2)+3,FIND("REF",A2)FIND("TO",A2)6)&"td"
View attachment 69454
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Extracted Text Between Delimiters" = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BetweenDelimiters(_, "TO", "REF"), type text}})
in
#"Extracted Text Between Delimiters"
A  
1  Column1 
2  ABC Ltd 
3  ACME LLC 
4  A1 PUMPS Ltd 