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

`=DATEVALUE(MID(B5,SEARCH("/??/",B5,1)-2,10))`

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

`=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.

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

- 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?**

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

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

`=DateSeparate(B5)`

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

`=IF(DateSeparate(B5)=0,"",DateSeparate(B5))`

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

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

`=INT(B5)`

- 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`

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

**<< Go Back to Date and Time | Split | Learn Excel**