Extract date from the string (text)

Alla Suzdaltsev

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.

Leah Rankin

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.

Leah Rankin

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

Wow, this is great. THANK YOU!!!

Janice Leone

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

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.

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

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