How to Extract the Second Last Date from a String in Excel Formula

Sometimes, we may encounter text strings that contain multiple dates along with other characters, such as:

DA1234621222 Corp|0|3|;2;1;3;8;01/01/2024;2;20000.00;2;2;1000000.00;|15|11/30/2023|
US12356HNM19 Corp|0|3|;2;4;3;1;04/29/2024;2;21000.000;2;.000;5;10/29/2024;2;21000.000;2;.000;5;04/29/2025;2;21000.000;2;.000;5;10/29/2025;2;2100.000;2;1000000.00;|15|10/31/2023|

In such cases, we may want to extract the second last date from each string, which are:

01/01/2024
10/29/2025

To do this, we can use a combination of excel functions, such as MID, SEARCH, DATEVALUE, and IFERROR. These functions can help us locate the position of the dates, isolate the date part, convert the date string to a date value, and handle any errors.

Procedures

The general steps to extract the second last date from a string in excel formula are:

1. Use the SEARCH function to find the position of the last slash (/) in the string, which indicates the end of the last date.
2. Use the MID function to extract the 10 characters before the last slash, which should be the second last date in the format MM/DD/YYYY.
3. Use the DATEVALUE function to convert the extracted date string to a date value that excel can recognize and format.
4. Use the IFERROR function to return a blank or a custom message if the extracted date is not valid or the string does not contain any dates.

Explanation

To illustrate the above steps, let’s use the following example:

The formula we can use in cell B2 is:

=IFERROR (DATEVALUE (MID (A2,SEARCH (\"/??/\",A2,1)-2,10)),\"\")

Then we can copy and paste the formula to the rest of the cells in column B.

Here is how the formula works for cell B2:

• The SEARCH function returns the position of the last slash in cell A2, which is 67.
• The MID function extracts the 10 characters before the last slash, which is 01/01/2024.
• The DATEVALUE function converts the extracted date string to a date value, which is 44661.
• The IFERROR function returns the date value if it is valid, otherwise it returns a blank.

We can then format the date values in column B as dates using the Format Cells dialog.