MID + Find Formula

[Carlos_Gonzalez]

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

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!

[adam]

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!

[Carlos_Gonzalez]

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.

Best!