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

 

[arthurbr]

Will the format always be the same, meaning your data is always after the second - ? ( whatever the length)

 

[John44]

Not sure. But the J string is always preceded and finished with the spaces and dashes.

 

[Cubist]

Post 5-10 entries in a copy-able format (not picture). One sample is not representative enough.
Which version of Excel are you using?

 

[John44]

Job Code	Description
=IF((ISERROR(MID(G2,(FIND(" - J",G2))+3,4))),"",(MID(G2,(FIND(" - J",G2))+3,4)))	GoDaddy - July Bill
J000	Fuel card - Part 2/5, FUEL CARD inv. 9003230422, date: 15/05/2022 - J000063 - Marine View, Fleetwood
J129	PayByPhone Parking - Premier House; 07/03/2024; 6490 - J129
J132	KaydeeSayfa - PO5598, Church Road, Crosby, 1st retention - J132

 

[John44]

How's that? I've shown the formula in the top one. No clue on version but I presume up to data as MS office 365 subscription is up to date

 

[arthurbr]

Could there possibly by a "J" somewhere else in the description?

 

[John44]

For sure yes. I have thousands of data rows

 

[John44]

Also, the row 1 is bringing back text i.e. July!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)