Find and extract text in a cell

[Eldublee]

I have a bank statement with 1000 customer payments.

Each customer uses a unique identifyer with their payment hpwvere this unique text is not in the same ppace of a text string as other customers (inv123 google llc ref 12345) another customer may be (microsoft pty ltd inv321. Ref 5678)

If google uses google llc in their description everytime they make payment, then “google llc” or “Microsoft pty ltd” will be the part of the text string i need to locate and extract regardless of where in the text string it is

 

[Eldublee]

Using the data below I want to find a formula to do the following

1. Look at the Text in a Cell EG "Devoli Ltd Devoli Limited 0685465"
2. Review the cell above it to see if it matches

Eg:
NZD CREDITORDEVOLI LTD NZL00003908 DEVOLI TRADING A 0000000
and
Devoli Ltd Devoli Limited 0685465
and
NZD CREDITORDEVOLI LTD NZL00003763 DEVOLI TRADING A 0000000

From this example, we can see "Devoli Ltd" is common text in all 3 cells but in different places and different cases, within the text string of each cell.

1. Once I have found the Text match from the cell above, I want to return the matched text "Devoli Ltd" into a different column headed "Payment Descriptor ID"
2. Once I have the matched text in a separate column I have the customers "unique payment descriptor id"
3. I then use that "Payment Descriptor ID" to locate the customers account number.

I have tried =LEFT([Cell Ref],27) and received the below results:

NZD CREDITORDEVOLI LTD
Devoli Ltd Devoli Limited 0
Devoli Ltd Devoli Limited 0

This does not allow for text descriptors in the middle of the text string such as "Transferwise" for account 22.
NZL00003034 301238783TW0 TR
AUS00018172 Anycast Holding
NZL00003915 389631978TW0

Using Wildcards / XLookup =XLOOKUP("*"&$F5&"*","*"&$F4&"*",$E5,LEFT(F5,27),2)

not found

Devoli Ltd Devoli Limited 0

Devoli Ltd Devoli Limited 0

NZD CREDITORDEVOLI LTD NZL0

Account	Transaction narrative
15	NZD CREDITORDEVOLI LTD NZL00003908 DEVOLI TRADING A 0000000
15	Devoli Ltd Devoli Limited 0685465
15	Devoli Ltd Devoli Limited 0301360
15	NZD CREDITORDEVOLI LTD NZL00003763 DEVOLI TRADING A 0000000
16	FetchTV Management FetchTV Manageme 0374306
16	FetchTV Management FetchTV Manageme 0306721
16	FetchTV Management FetchTV Manageme 0280079
17	SPECTRUM NETWORKS SPECTRUM NETWORK 0846284
17	SPECTRUM NETWORKS SPECTRUM NETWORK 0512711
17	SPECTRUM NETWORKS SPECTRUM NETWORK 0462316
18	TWO DEGREES NETWORKS LIMITED AUS00017486 AUS00018167
22	NZL00003034 301238783TW0 TRANSFERWISE 0000000
22	AUS00018172 Anycast Holdings 0406462
22	NZL00003915 389631978TW0 TRANSFERWISE 0000000
22	ANYCAST HOLDINGS PTY LTD SIN00003237 /ROC/TW371997198
22	ANYCAST NETWORKS ANYCAST HOLDINGS 0509549
22	ANYCAST NETWORKS ANYCAST HOLDINGS 0296920
22	NZL00003770 371712082TW0 TRANSFERWISE 0000000
23	SIS AUS00018173 CBA 0259358
23	AUS00018863 CBA 0487585
24	AUS00018864 NEXON 0755722
24	NEXON - 24 NEXON ASIA PACIF 0882597
24	AUS00018174 NEXON 0507156
24	AUS00017493 NEXON 0594114
25	OVER THE WIRE PTY LTD PAY NZL00003916
25	AUS00018865 Over The Wire 0424128
25	OVER THE WIRE PTY LTD PAY NZL00004052
25	AUS00018175 Over The Wire 0604586
25	AUS00017494 Over The Wire 0387199
25	OVER THE WIRE PTY LTD PAY NZL00003771
28	AUS00016812 REDD Digital Ser 0152736
32	HIGHWINDS NETWORK GROUP INC INV - AUS00018183
32	HIGHWINDS NETWORK GROUP INC INV AUS00017502
34	AUS00018184 MICRON DATA 0727411
34	AUS00013464 MICRON DATA 0804263