How to Extract Date From a Text String in Excel

You might find yourself working with spreadsheets that have jumbled up data. For example, a single cell holds up all of your information instead of distributing it among different cells or adding columns. You might also come across data from another system where Excel does not easily recognize the dates. It can be hard when you have to extract a date from a string of texts. Sometimes you find it a challenge as the date might even be in the wrong format and position in the first place.

So, how do we extract a date from a text string? In this article, we are going to explain to you the steps to achieving this.

Method 1: Use Find & Replace

We make mistakes by placing full stop characters to separate the year, month, and day. Doing so is a mistake as Excel will not recognize the entry as a date but as a text. You can solve this using the find and replace tool, replace the dots with slashes where Excel will automatically recognize said values as dates. Here's what to do;

1. Select the columns to convert the texts to date values.

2. Click the Home tab and then the Find & Select option.

3. In the open window, select Replace option.

4. In the 'Find what' fields, type the full stop icon, while in the 'Replace with' field, type the slash icon.

5. Lastly, click Replace All. All full stops will be converted to slashes by Excel.

Method 2: Use Text to Columns

1. Select your range of values

2. Go to the Data tab in the main menu ribbon.

3. Select Text to Columns. A display wizard will appear. Click 'Next' on the first two steps. In the third step, choose the Date field and the date format in the drop-down list provided.

Once you click finish, the data will be extracted to the next empty  cell

Method 3: Using Excel MID, SEARCH & DATEVALUE Functions

1. Type the formula below in the cell you are working on (e.g. Cell C5) and hit Enter.

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

2. As a result, you get a numeric number since excel stores dates as numbers.

3. The number in the previous step is in General Number format and is to be converted into Date format. So, select Cell C5 and press Ctrl +1 to bring the Format Cells dialog.

4. When the Format Cells dialog pops up, go to the Number tab, then select Category: Date and choose the date Type, and press OK.

5. When the Date Number format is applied, use the Fill Handle (+) to separate the data generated from the rest of the texts.

6. When a date included in the string is not a valid date, excel returns the #VALUE!

7. To avoid encountering the error, the above formula is passed within the IFERROR function and the final formula used is:

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

8. Therefore IFERROR function returns a blank when the result is an error.

Method 4: Using a combination of Excel INDIRECT, LEN, MIN, FIND & ROW Functions

1. In Cell C5, type the below formula 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 – MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789”,1)),LEN(B5)+1)))

2. As a result, the date part of the text string in Cell B5 is separated. Now use the Fill Handle (+) to copy the formula used in the above step to the rest remainder of the cells.

Method 3: Using Excel VBA

This method applies the VBA User Defined Function to separate the date from the text.

1. First, access the VBA window by pressing Alt + F11.

2. When the VBA window pops up, right-click on VBAProject > Insert >Module.

3. A new module will be created. Type the below code in it.


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 

4. Now go to the worksheet containing the text string just as the UDF was created and type in the formula =DateSeparate(B5) in Cell C5 and press Enter.

5. However, in a case of a text with an irregular date format, we get no valid date. To avoid the problem, the IF function is added to the formula.

                        =IF(DateSeparate(B5)=0,””,DateSeparate(B5))

Conclusion

This article has provided ways of extracting data from a string of text. You can choose either of the methods to modify your spreadsheet and still get the same result.