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

 

[rpaulson]

vba option

acct_no= Split(Range("A1"), "~")(4)
msgbox acct_no

 

[Fluff]

Will the account number always be between the 4th & 5th ~

 

[EmmaTM]

Will the account number always be between the 4th & 5th ~

Hi - yes it seems to be the case.

 

[Fluff]

Ok, how about

Fluff.xlsm
	A	B
1
2	Contractor Timesheet Pay~GEN000000000~M100004CA~E Pppp~79347~SSS Ltd~2023~1	79347
Data

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

 

[Automatrix]

Power Query solution:

This will split the string into 8 columns, but you can just delete the 7 other columns as the next step.

 

[rpaulson]

ring into 8 columns, but you can just delete the 7 other columns as the next st

Automatrix, how do I implement your solution?

 

[Automatrix]

Automatrix, how do I implement your solution?

1. Have you data in a table. Go to Data --> Get data --> From table/range. This will make a query in Power Query, and open the Query editor window, your new best friend
2. Right click on the column with the data, choose Split column, then Split Column by Delimiter, and make it look like the image.
3. This will probably yield 8 columns, one of which contains the desired data (if indeed it is always between the 4th and 5th ~). Delete the other columns, and load the query as a new table.

I hope this works for you, otherwise let us know where it goes wrong.

 

[rpaulson]

oops, I missed the part of your post about PQ,

 

[EmmaTM]

Ok, how about

Fluff.xlsm
	A	B
1
2	Contractor Timesheet Pay~GEN000000000~M100004CA~E Pppp~79347~SSS Ltd~2023~1	79347
Data

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

Hey - that worked on the majority of the items but not these even though they are within the 4th & 5th ~

Employers NIC Debit~GEN000000000~M10000PA~N Bbbbbb~79792~D Ltd~2023~1
Contractor Timesheet Pay~GEN00000000~M20000CA~S Hhhhhh~80041~T Ltd~2023~1