Extract date from the string (text)

Options
Alla Suzdaltsev
Alla Suzdaltsev ✭✭✭
edited 01/20/22 in Formulas and Functions

Can anyone recommend a formula to extract date from the string of any length? Example: "Property: Maple, Ltd Balance Due 1/1/2022" or "12/1/2021 Balance due for Napa" .

The string could be of any length and the date could be placed anywhere within the text. But it will always have two "/" characters. Unfortunately, date length is not guaranteed either. It may be 01/01/22 or 1/1/2022. Any ideas and recommendations are welcomed.

Tags:

Best Answer

  • Leah Rankin
    Leah Rankin ✭✭✭
    Answer ✓
    Options

    Unfortunately, there just is not a good way to do this. The best I can do for you is a MID(FIND()), but let me warn you, it is UGLY:

    =MID([Primary Column]@row, IF(FIND("/", [Primary Column]@row) <= 2, FIND("/", [Primary Column]@row) - 1, FIND("/", [Primary Column]@row) - 2), IF(FIND("/", [Primary Column]@row) <= 2, 1 + FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1) - FIND("/", [Primary Column]@row), 2 + FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1) - FIND("/", [Primary Column]@row))) + MID([Primary Column]@row, FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1), IF(FIND(" ", [Primary Column]@row, FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1)) = 0, 5, FIND(" ", [Primary Column]@row, FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1)) - FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1)))

    Where [Primary Column]@row is your cell with the string in it.

Answers

  • Leah Rankin
    Leah Rankin ✭✭✭
    Answer ✓
    Options

    Unfortunately, there just is not a good way to do this. The best I can do for you is a MID(FIND()), but let me warn you, it is UGLY:

    =MID([Primary Column]@row, IF(FIND("/", [Primary Column]@row) <= 2, FIND("/", [Primary Column]@row) - 1, FIND("/", [Primary Column]@row) - 2), IF(FIND("/", [Primary Column]@row) <= 2, 1 + FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1) - FIND("/", [Primary Column]@row), 2 + FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1) - FIND("/", [Primary Column]@row))) + MID([Primary Column]@row, FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1), IF(FIND(" ", [Primary Column]@row, FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1)) = 0, 5, FIND(" ", [Primary Column]@row, FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1)) - FIND("/", [Primary Column]@row, FIND("/", [Primary Column]@row) + 1)))

    Where [Primary Column]@row is your cell with the string in it.

  • Alla Suzdaltsev
    Options

    Wow, this is great. THANK YOU!!!

  • Janice Leone
    Options

    This formula is ugly but it's amazing! I used it and just changed the cell for my own purposes. Thanks for asking this question Alla, and huge thanks to Leah for answering!! It works!!

  • Hollycarter
    Options

    Hi Leah, thank you so much for providing this formula! Could you help me tweak it slightly? I am trying to extract the date from an automation that I have integrated, and the dates will be populated in the following format: 2023-07-06T15:39:20.9700000Z

    Currently, I have just changed your formula to instead search for dashes, and to search in the desired column:

    =MID([First Sent]@row, IF(FIND("-", [First Sent]@row) <= 2, FIND("-", [First Sent]@row) - 1, FIND("-", [First Sent]@row) - 2), IF(FIND("-", [First Sent]@row) <= 2, 1 + FIND("-", [First Sent]@row, FIND("-", [First Sent]@row) + 1) - FIND("-", [First Sent]@row), 2 + FIND("-", [First Sent]@row, FIND("-", [First Sent]@row) + 1) - FIND("-", [First Sent]@row))) + MID([First Sent]@row, FIND("-", [First Sent]@row, FIND("-", [First Sent]@row) + 1), IF(FIND(" ", [First Sent]@row, FIND("-", [First Sent]@row, FIND("-", [First Sent]@row) + 1)) = 0, 5, FIND(" ", [First Sent]@row, FIND("-", [First Sent]@row, FIND("-", [First Sent]@row) + 1)) - FIND("-", [First Sent]@row, FIND("-", [First Sent]@row) + 1)))

    However, this is what it is returning: "23-07-06T1", I just need it to not include the "T1." Any help would be greatly appreciated!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hey @Hollycarter

    Will your date format always be the exact same?

    YYYY-MM-DD-extra?

    If so, we could likely use a more concise formula to grab the Year, Month, and Day. For example, in a Date type of column:

    =DATE(VALUE(LEFT([First Sent]@row, 4)), VALUE(MID([First Sent]@row, 6, 2)), VALUE(MID([First Sent]@row, 9, 2)))

    Cheers,

    Genevieve

  • Hollycarter
    Options

    Hey Genevieve, thank you so much! This is exactly what I needed.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!