Hey, guys -
I’m stumped on this MID based formula that should be fairly easy. Can someone maybe advise on the best way to accomplish the following?
I have job numbers that are formatted like this: 1206-0317-JORGE
The first number is sequential. So our 876th job would have a job number that looked like this: 876-0314-HEY. I don’t want to have to add a zero in front of every job; too easy
The middle string is the date in MMYY format. Luckily this string is always four characters.
Anyway, I need a formula that looks at both of these job numbers and takes out the date string and one of the "-"s to form a “short code” that looks exactly like this: 1206-JORGE and 876-HEY.
How can this be done given the inconsistencies with job number formatting? There’s gotta be a way. My problem is (I think) I’m not using the FIND formula correctly.
Thank you for your time!