Formula extract help

John44

Board Regular
Joined
Oct 1, 2014
Messages
60
Office Version
  1. 365
Platform
  1. Windows
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



1737036239962.png
 
I understand, the above mostly works for 100,000 rows of data. Thanks for your help!
 
Upvote 0
Maybe I'm misunderstanding what you want. Do you want the J9-1 to return J91?
 
Upvote 0
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
 
Upvote 0
I had the impression that it was. Try:
Edited
Excel Formula:
=LET(t,TEXTSPLIT(SUBSTITUTE(B2,"-","|")," "),s,--TEXTAFTER(t,"J"),XLOOKUP(1,(LEFT(t)="J")*(ISNUMBER(s)),"J"&s,""))
 
Upvote 0
I've edited post#24 realizing that afterwards. Try again.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top