Formula extract help

[John44]

Hi,

I have a whole list of entries like this. I need to pull out the job reference i.e. the J code. As the J code can vary from 5 to 7 characters then I can't use the FIND and MID to pull out the entire ref sometimes. Also, it pulls out descriptions for non job entries like ' - June' which I don't need. I need it to start at the J, determine it's a value after the J and then return up to the space before the dash after the J code. Does that make sense?

Can anyone help?

Thanks

John

 

[John44]

Done, 365 should show.

 

[arthurbr]

If in same column, Data-Text to columns use - as delimiter

 

[John44]

I thought of that but the data will be dropped in more than once and will change rows i.e. it's a dynamic usable spreadsheet

 

[Cubist]

Try:

Book4
	A	B
1	Job Code	Description
2		GoDaddy - July Bill
3	J123	GoDaddy - July Bill - John - J123 - Joker
4	J000063	Fuel card - Part 2/5, FUEL CARD inv. 9003230422, date: 15/05/2022 - J000063 - Marine View, Fleetwood
5	J129	PayByPhone Parking - Premier House; 07/03/2024; 6490 - J129
6	J132	KaydeeSayfa - PO5598, Church Road, Crosby, 1st retention - J132
Sheet3

Cell Formulas
Range	Formula
A2:A6	A2	=LET(t,TEXTAFTER(TEXTSPLIT(B2," "),"J"),CONCAT(IF(ISNUMBER(--t),"J"&t,"")))

 

[John44]

Wow! That nearly works 100% thank you. It falls down on these types. Where the job number is manually typed in twice and what ever J9 is. Looks like something else. I don't have familiarity with these guys enough.

Job Code	Job Name	Description
J105J105	#N/A	Drive Card Europe - J105 (GEM Foston) - J105
J9-1	#N/A	Interest on loan and HP loan agreements - Interest (J9-1 LE69 LHR)

 

[Cubist]

Try:
Note the J9-1 works because Excel recognizes 9-1 as a date i.e. September 1. If you have something that does not register as a date, it'll fail.

Book4
	A	B
1	Job Code	Description
2		GoDaddy - July Bill
3	J123	GoDaddy - July Bill - John - J123 - Joker - J123
4	J000063	Fuel card - Part 2/5, FUEL CARD inv. 9003230422, date: 15/05/2022 - J000063 - Marine View, Fleetwood
5	J129	PayByPhone Parking - Premier House; 07/03/2024; 6490 - J129
6	J132	KaydeeSayfa - PO5598, Church Road, Crosby, 1st retention - J132
7	J105	Drive Card Europe - J105 (GEM Foston) - J105
8	J9-1	Interest on loan and HP loan agreements - Interest (J9-1 LE69 LHR)
Sheet3

Cell Formulas
Range	Formula
A2:A8	A2	=LET(t,TEXTAFTER(TEXTSPLIT(B2," "),"J"),XLOOKUP(TRUE,ISNUMBER(--t),"J"&t,""))

 

[John44]

you guys are too good! Thank you. The biggest problem now is that the data coming from different sources can J000132 or J132 by the looks of it. I guess I could create a static look up table for the projects though to refer to that.

Job Code	Job Name	Description
J000132	#N/A	Daniel Vivian - Week: 01-07/01/2024; Description: 4 days @£220; J000132 - Church Road
J132	Church Road, Crosby	Fuel card - Church Road, Crosby - J132

 

[Cubist]

Book4
	A	B
1	Job Code	Description
2		GoDaddy - July Bill
3	J123	GoDaddy - July Bill - John - J123 - Joker - J123
4	J63	Fuel card - Part 2/5, FUEL CARD inv. 9003230422, date: 15/05/2022 - J000063 - Marine View, Fleetwood
5	J129	PayByPhone Parking - Premier House; 07/03/2024; 6490 - J129
6	J132	KaydeeSayfa - PO5598, Church Road, Crosby, 1st retention - J132
7	J105	Drive Card Europe - J105 (GEM Foston) - J105
8	J9-1	Interest on loan and HP loan agreements - Interest (J9-1 LE69 LHR)
9	J132	Fuel card - Church Road, Crosby - J132
10	J132	Daniel Vivian - Week: 01-07/01/2024; Description: 4 days @£220; J000132 - Church Road
Sheet3

Cell Formulas
Range	Formula
A2:A10	A2	=LET(t,TEXTAFTER(TEXTSPLIT(B2," "),"J"),XLOOKUP(TRUE,ISNUMBER(--t),"J"&IF(ISNUMBER(SEARCH("-",t)),t,--t),""))

 

[John44]

Thank you so much guys. 99% there for sure. I have mostly three types of entries now that it fails on. Hundreds of each of these types.

 

[Cubist]

You should understand that Excel is not human and it only follows instructions so there needs to be a consistent rule. The rule that it has been following is that extract anything that starts "J" and follows by anything that it recognizes as a number.
The second and third case should work as it follows these rules where:

• 9-2 is a date (serial number)
• -1 is a number

The first case fails because it does not follow these rules. Until you can come up with a consistent rule across all entries, you can't get them all.