How to Separate Date from Text in Excel (4 Methods)

In this article, I will discuss how you can separate the date from text strings in Microsoft Excel. While working with excel, often we receive spreadsheets that contain dates along with other texts. In such cases, we have to extract the date from the text string. Luckily, we can do the task using excel features and functions. So, let’s go through the article to separate dates from a text string.

1. Combining MID, SEARCH & DATEVALUE Functions to Separate Date from Text in Excel

Now I will extract the date part from some text strings using excel MID, SEARCH, and DATEVALUE functions. Let’s consider the following dataset.

Now, from the above text strings, I will isolate the date part.

Steps:

• Type the below formula in Cell C5 and hit Enter.

• Consequently, we will get the below result. Here we received a numeric number as excel stores dates as number

• The above result is in General Number format. So, we have to convert the value into Date format. To do that, select Cell C5, and press Ctrl + 1 to bring the Format Cells dialog.
• When the Format Cells dialog appears, go to the Number tab, select Category: Date and choose the date Type, and press OK.

• Once the Date Number format is applied, we can see that the previous numeric value is changed to a date.
• Now, as usual, use the Fill Handle (+) to separate the dates from the rest of the texts.

• Then, we will get the below output. Here is a problem we face with the text string of Cell B8. As the date included in the string is not a valid date, excel returns the #VALUE! error.

• So to avoid the above error, we will pass the above formula within the IFERROR function. The final formula we have used is:

So, above is our final output. We can see that the IFERROR function returns a blank (“ “) when the result is an error.

🔎 How Does the Formula Work?

2. Joining Excel INDIRECT, LEN, MIN, FIND & ROW Functions to Separate Date from Text

In this method, I will use the combination of INDIRECT, LEN, MIN, FIND, and ROW functions to separate dates from text strings. Suppose we have the below dataset containing several text strings that contain dates too.

Now I will extract only date portions from the above text.

Steps:

• First, type the below formula in Cell C5 and press Enter.

• As a result, we will get the below result. We can see the date portion of the text of Cell B5 is separated as shown in the below screenshot.
• Next use the Fill Handle (+) to copy the above formula to the rest of the cells.

• Finally, we will get the below output. All the dates from each text string are separated.

🔎 How Does the Formula Work?

3. Applying Excel Text to Columns Tool to Separate Date from Text

This time I will use the ‘Text to Columns’ feature of excel to separate a date from a text string. Let’s say we have the below dataset containing several text strings along with date values.

Let’s follow the below steps to separate the dates from the above text strings.

Steps:

• First of all, select the column where we have the text strings (here, column B).

• Next, go to Data > Text to Columns.

• As a result, the Text to Columns window appears. Now choose the Delimited option and press Next.

• From the Delimiters section, put a checkmark on Space and click Next.

• Then from the Column Data Format section, click on the Do not import column (skip), choose the destination location, and press Finish.

• Finally, here is the output we receive. All the dates are extracted from the corresponding text strings.

4. Using VBA Macro to Separate Date from Text

In this method, I will use VBA User Defined Function (UDF) to extract the date part from test strings.

Steps:

• First to get the VBA window, from the ribbon go to Developer > Visual Basic. You can use Alt + F11 too to bring the VBA window.

• When the VBA window shows up, right-click on VBAProject > Insert > Module.

• Then type the below code in the newly created Module.

Function DateSeparate(st As String)
On Error GoTo eH
Dim j As Integer, d As Date, ar() As String, tmp As String
d = 0
ar = Split(st)
For j = LBound(ar) To UBound(ar)
tmp = ar(j)
If IsDate(tmp) And Len(tmp) > 5 Then
d = DateValue(tmp)
Exit For
End If
Next j
cont:
DateSeparate = d
Exit Function
eH:
d = 0
Resume cont
End Function

• As we created the UDF, now go to the worksheet that contains the text strings with text. Now, type the below formula in Cell C5, and press Enter.

• As a result, we will get the below output (after converting to Date Number format). However, for the text of Cell C8, we do not get any valid date as the text does not have a regular date format.

• To solve the above problem, we will wrap the UDF with the IF function. The formula is:

Here, the IF function returns blank (“ “) when the result of UDF is not a valid date.

Read More: How to Split Date and Time Using VBA in Excel

How to Separate Date and Time in Excel

You can split the date and time from date-time using the INT function in Excel.

For example for the above date-time data, follow the below steps.

Steps:

• First, convert the date-time data into General Number format.
• Next, use the below formula to get the date part of the date-time.

• Here, the above result is in General Number format. convert it to Date format.

• Next, to find the time part of the date-time use the following formula.

Read More: How to Separate Time Using Formula in Excel

Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

Conclusion

In the above article, I have tried to discuss several methods to separate date values from a text in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Related Article

• How to Separate Date and Time in Excel without Formula
• How to Separate Date in Excel Using Formula
• How to Display Day of Week from Date in Excel