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.