Formula Tool: Dynamically searching for prior year in a string, and filter for

[agodwin1250]

Hi everyone,

I am currently trying to figure out a way to dynamically search and extract a specific row of data within a table. The row contains only numbers which change every time we will need to run the workflow, and the only identifier is the description column in that the cell states "Outstanding Dec 20PY" with PY being the prior year.

The issue is there will always be another cell that states "Outstanding Dec 20XX" for the beginning balance that is the current year minus two years - and I do not want to extract this row.

I have tried embedding variations of the (DateTimeYear() formula within an IF statement to try and accomplish this, but so far to no avail.

Would anyone know the formula to write in order to dynamically search for the prior year within a string, and embedding this within an IF/THEN statement in order to extract the specific row? Or if there is another way to extract this information?

[DavidP · Accepted solution]

Something like this?

[agodwin1250]

Exactly what I was looking for, thanks!

Now looking at this formula, I believe my previous errors were due to "tonumber" rather than "tostring" within the formula.