How can I extract a date from a text string in Google Sheets?

By /

To extract a date from a text string in Google Sheets, you can use the DATEVALUE function. This function converts a date string into a date value that can be used in mathematical calculations or formatting. By using this function, you can extract the date from a text string and use it in your spreadsheet for various purposes, such as sorting and filtering data by date or creating charts and graphs based on the date. Simply input the cell containing the text string into the DATEVALUE function and the extracted date will be displayed in the desired format.

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

=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:

x

Related terms:

  1. Excel: Extract Date from Text String How do i extract a date from a text string?
  2. Google Sheets: Extract Text Between Two Characters
  3. Google Sheets: Extract Text Before a Character
  4. Google Sheets: Extract Text After a Character
  5. How do I use LEFT to extract text before space in Google Sheets?
  6. How can I extract text between quotes in Google Sheets?
  7. How to extract numbers from string in Google Sheets?
  8. How to Extract Email Address from Text String in Excel?
  9. How to extract a year from a date in Google Sheets?
  10. Count Specific Characters in Google Sheets How to count specific characters in google sheets?