Skip to main content

MID + Find Formula

C

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!

    2 replies

    A
    • adam11
    • Participating Frequently
    • 6 replies
    • July 16, 2017

    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!

    C

    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!

    A
    1 person likes this
    • A
      adam11

    Reply


    Most helpful members this week

    Terms & Conditions