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

Get FREE Advanced Excel Exercises with Solutions!

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.

Trim Date from Text Using Excel MID, SEARCH & DATEVALUE Functions

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

Steps:

  • Type the below formula in Cell C5 and hit Enter.
=DATEVALUE(MID(B5,SEARCH("/??/",B5,1)-2,10))

Trim Date from Text Using Excel MID, SEARCH & DATEVALUE Functions

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

Trim Date from Text Using Excel MID, SEARCH & DATEVALUE Functions

  • 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.

Trim Date from Text Using Excel MID, SEARCH & DATEVALUE Functions

  • 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.

Trim Date from Text Using Excel MID, SEARCH & DATEVALUE Functions

  • 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:
=IFERROR(DATEVALUE(MID(B5,SEARCH("/??/",B5,1)-2,10)),"")

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?

  • SEARCH(“/??/”,B5,1)

Here, the SEARCH function finds the location of the date (“/??/”) in Cell B5 and returns:

{17}

  • MID(B5,SEARCH(“/??/”,B5,1)-2,10))

Then the MID function isolates the date part of Cell B5 which is:

{10/04/2022}

  • DATEVALUE(MID(B5,SEARCH(“/??/”,B5,1)-2,10))

Next, the DATEVALUE function converts the resulting date (10/04/2022) of the MID formula into a numeric number:

{44661}

  • IFERROR(DATEVALUE(MID(B5,SEARCH(“/??/”,B5,1)-2,10)),””)

At last, the IFERROR function returns a blank (“ “) when the result is an error, otherwise the formula returns the numeric representation of the separated date part. Here for Cell B5, the result is:

{44661}


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.

Combine Excel INDIRECT, LEN, MIN, FIND & ROW Functions to Separate Date from Text

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

Steps:

  • First, type the below formula in Cell C5 and press Enter.
=MID(B5,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",1)),LEN(B5)+1)),LOOKUP(1,0*MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),ROW(INDIRECT("1:"&LEN(B5)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",1)),LEN(B5)+1)))

Combine Excel INDIRECT, LEN, MIN, FIND & ROW Functions to Separate Date from Text

  • 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.

Combine Excel INDIRECT, LEN, MIN, FIND & ROW Functions to Separate Date from Text

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

🔎 How Does the Formula Work?

  • MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″,1)),LEN(B5)+1))

Here above part of the formula locate where the date part starts in the text of Cell B5 and returns:

{15}

  • LOOKUP(1,0*MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1),ROW(INDIRECT(“1:”&LEN(B5))))

Now, this part of the formula returns the whole length of the text string of Cell B5:

{24}

  • MID(B5,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″,1)),LEN(B5)+1)),LOOKUP(1,0*MID(B5,ROW(INDIRECT(“1:”&LEN(B5))),1),ROW(INDIRECT(“1:”&LEN(B5)))) + 1 – MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789”,1)),LEN(B5)+1)))

At last, the above formula returns the date part from the text of Cell B5 which is:

{10/04/2022}


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.

Excel ‘Text to Columns’ Option to Separate Date from Text

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).

Excel ‘Text to Columns’ Option to Separate Date from Text

  • Next, go to Data > Text to Columns.

Excel ‘Text to Columns’ Option to Separate Date from Text

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

Excel ‘Text to Columns’ Option to Separate Date from Text

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

Excel ‘Text to Columns’ Option to Separate Date from Text

  • 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.

Use Excel VBA to Separate Date from Text

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

Use Excel VBA to Separate Date from Text

  • 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

Use Excel VBA to Separate Date from Text

  • 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.
=DateSeparate(B5)

Use Excel VBA to Separate Date from Text

  • 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.

Use Excel VBA to Separate Date from Text

  • To solve the above problem, we will wrap the UDF with the IF function. The formula is:
=IF(DateSeparate(B5)=0,"",DateSeparate(B5))

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.

How to Split Date and Time 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.
=INT(B5)

How to Split Date and Time in Excel

  • 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.
=B5-C5

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hosne Ara
Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo