Google Sheets: How to Extract Date from Text String


You can use the following formula to extract a date from a text string in Google Sheets:

=MID(" "&A2,FIND("/"," "&A2,1)-2,10)

The following example shows how to use this formula in practice.

Example: Extract Date from Text String in Google Sheets

Suppose we have the following column of text strings in Google Sheets that all contain a date somewhere in the string:

Suppose we would like to extract the date from each text string.

To do so, we can type the following formula into cell B2:

=MID(" "&A2,FIND("/"," "&A2,1)-2,10)

We can then click and drag this formula down to each remaining cell in column B:

Column B now contains the date from each text string in column A.

Note that if your date is in a different format than mm/dd/yyyy then you can modify the formula.

For example, if your date is in the format mm-dd-yyyy then you can replace the slash in the FIND function with a dash:

=MID(" "&A2,FIND("-"," "&A2,1)-2,10)

This allows the formula to find each date that uses a mm-dd-yyyy format instead.

How This Formula Works

Recall the formula that we used to extract the date from the text string in the first example:

=MID(" "&A2,FIND("/"," "&A2,1)-2,10)

Here is how this formula works:

First, we use the FIND function to return the position of the first slash in each text string. 

For example, in the string My birthday is on 10/12/2023 this function returns 22.

Then we subtract 2 to get 20.

Then we use the MID function to return all of the text in the middle of the string starting from position 20 to 10 additional characters.

Thus, the formula returns 10/12/2023.

We use this same logic to extract the date from each text string.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

How to Find Most Recent Date in Google Sheets
How to Find the Closest Date in Google Sheets
How to Combine Date & Time in Google Sheets

Leave a Reply

Your email address will not be published. Required fields are marked *