How to find a code within a string of text

[EmmaTM]

Hi, I have a column with a string of text that can have different numbers of characters. I want to be able to extract the account number shown below in red so that I can do a look up with a list of customer account codes on another tab. I cannot use left or right as I said the numbers before and after will always be different.

Contractor Timesheet Pay~GEN000000000~M100004CA~E Pppp~79347~SSS Ltd~2023~1

Thank you
Emma

 

[Fluff]

In what way didn't it work?
It works for me

Fluff.xlsm
	A	B
1
2	Contractor Timesheet Pay~GEN000000000~M100004CA~E Pppp~79347~SSS Ltd~2023~1	79347
3	Employers NIC Debit~GEN000000000~M10000PA~N Bbbbbb~79792~D Ltd~2023~1	79792
4	Contractor Timesheet Pay~GEN00000000~M20000CA~S Hhhhhh~80041~T Ltd~2023~1	80041
Data

Cell Formulas
Range	Formula
B2:B4	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2,"~","</m><m>")&"</m></k>","//m[5]")

 

[EmmaTM]

I got #VALUE! in those 2 mentioned above

 

[Fluff]

As you can see from post#11 it works for me, but try the formula in col C

Fluff.xlsm
	A	B	C
1
2	Contractor Timesheet Pay~GEN000000000~M100004CA~E Pppp~79347~SSS Ltd~2023~1	79347	79347
3	Employers NIC Debit~GEN000000000~M10000PA~N Bbbbbb~79792~D Ltd~2023~1	79792	79792
4	Contractor Timesheet Pay~GEN00000000~M20000CA~S Hhhhhh~80041~T Ltd~2023~1	80041	80041
Data

Cell Formulas
Range	Formula
B2:B4	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2,"~","</m><m>")&"</m></k>","//m[5]")
C2:C4	C2	=REPLACE(LEFT(A2,FIND("^",SUBSTITUTE(A2,"~","^",5))-1),1,FIND("^",SUBSTITUTE(A2,"~","^",4)),"")

 

[EmmaTM]

Perfect - thank you

 

[Fluff]

Glad we could help & thanks for the feedback.