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]

I understand, the above mostly works for 100,000 rows of data. Thanks for your help!

 

[Cubist]

Maybe I'm misunderstanding what you want. Do you want the J9-1 to return J91?

 

[John44]

No. I don't think any of those three rows/references pulling in J numbers are actual job numbers. They just happen to have J things in the descriptions

 

[Cubist]

I had the impression that it was. Try:
Edited

=LET(t,TEXTSPLIT(SUBSTITUTE(B2,"-","|")," "),s,--TEXTAFTER(t,"J"),XLOOKUP(1,(LEFT(t)="J")*(ISNUMBER(s)),"J"&s,""))

 

[John44]

Seems to be putting the zeroes back into the Job Code.

 

[Cubist]

I've edited post#24 realizing that afterwards. Try again.

 

[John44]

Thats great. Works on those three a treat. I'm struggling to see anymore that are wrong now but I may be getting bleary eyed. Thank you for your help Cubist!