# Extract date from the string (text)

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:

• 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.

• 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.

• Wow, this is great. THANK YOU!!!

• 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!!

• 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!

• 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

• 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!