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.
Best Answer

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

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: 20230706T15: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: "230706T1", 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?
YYYYMMDDextra?
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
Categories
Check out the Formula Handbook template!