MID + Find Formula


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 :wink:

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!


Hey @Carlos_Gonzalez, I used REPLACE() instead of MID() and have it working. Using FIND() to get the starting index of the first hyphen and then replacing the middle date part with a zero length string ("").

REPLACE({Full Code}, FIND("-", {Full Code}), 5, “”)

You and right, and thankfully the MMYY date code in the middle can be hardcoded as we know it will always be the same length!


Adam, you are god amongst men! Thank you!!!

Def don’t think I would have figured this one out on my own so I appreciate the help.