# Homework: Can you extract dates from text?

Posted on August 17th, 2012 in Excel Challenges , Excel Howtos - 163 comments

So who is up for a challenge? Can you use only formulas and extract dates buried inside text?

## Your mission, if you choose to accept it,

2. In column C, write a formula such that you can extract the date in column B
3. If you succeed, post your solution here as a comment.
4. If you fail, drink some coffee, start afresh.

What are you waiting for? Start extracting!

## Need some help?

If you want some clues, check out below links:

## Want more challenges? We got’em too

You better have access to lots of coffee or something strong, cause it going to get tough.

PS: If you feel like cheating, here is a solution.

 One race, Every medalist ever – Interactive Excel Visualization Growing a Money Mustache using Excel [for fun]
 Written by Chandoo Tags: array formulas, date and time, downloads, homework, Learn Excel, Microsoft Excel Formulas Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 163 Responses to “Homework: Can you extract dates from text?”

1. milang says:

Something like
=IFERROR( DATEVALUE( MID( B4 , FIND( "/" , B4 ) - 2  ,10 ) ) , "" )

2. dane_andrew says:

works with cells with 07/01/1981:

=MID(B4,FIND("/",B4)-2,10)

3. Eamon says:

=MID(B4,MAX(1,FIND("/",B4)-3),11)

4. Aiks says:

This is the formula I used... it works assuming that the DOB will always be in MM/DD/YYYY format

=IFERROR(DATEVALUE(MID(B4,FIND("/",B4)-2,2)&"/"&MID(B4,FIND("/",B4,1+FIND("/",B4))-2,2)&"/"&MID(B4,FIND("/",B4,1+FIND("/",B4))+1,4)),"")

5. Ahmed says:

the is the formula, starting from C4 then drag down

=IFERROR(MID(B4,(FIND("/",B4)-2),10),"No DOB Found")

only drawback is that if a / is found without a date (if an entry in column B contained "/i love excel more than flying ninjas " for an example) the formula will return rubbish

can be solved by double checking we have two / and the distance between them is 2, if so, formula would be:

=IF(COUNTIF(B4,"*/**/*")=0,"No DOB Found",IFERROR(MID(B4,(FIND("/",B4)-2),10),"No DOB Found"))

If there are two / in a cell in column B and the distance between them is 2 and yet not a date this is doable but now we are getting ridiculous...

Regards,

• Pearl says:

It did work! but i've got one problem.. how can i remove the colon and semi colon?
here's my text:
ER: VICTORIA COURT (2/26/16)
=IFERROR(MID(F620,(FIND("/",F620)-2),10),"")
(2/26/16)

• dabeat says:

the problem is that the -2),10 portion of the formula indicates to return the text that starts 2 positions befor the / and ends 10 positions after it (that is a ful 10 character date: 12/12/2012). In your text, the 10 characters incude the two parentheses. You can fix it by:

A: changin the -2),10 portion of the formula for -1), 7, but that would return an incorrect value if the date is more than 7 characters long

B: this is my solution that wil work whenever you need to remove parenthesis. You substitute text ( and ) for nothing:

=SUBSTITUTE(SUBSTITUTE(IFERROR(MID(E11;FIND("/";E11)-2);10);"");"(";"");")";"")

I know there has to be a more elegant way to put it, but this will return the date like this: 2/26/16

6. Anup Agarwal says:

I think this works:

7. Faseeh says:

I think the simplest is with MID() and FIND():
`=IFERROR(MID(B4,FIND("/",B4,1)-2,10),"..")`

8. Misca says:

I'd use =IFERROR(DATEVALUE(MID(B4,FIND("/",B4)-2,10)),"")

9. Fowmy says:

=IFERROR(MID(B4,SEARCH("/",B4)-2,10),"")

10. Chandoo says:

Interesting approaches everyone. How would you write the formula if there can be a / without date too?

• Ahmed says:

11. Eduardo Quental says:

em português ficou assim:

Well i don't know quite how I would write it, but i'm sure there is an approach that would use an array and the code function. This would have the advantage that / & . & - and the numbers are all consecutive from 45 to 57.

One could probably use this to isolate a date. I would be interested to see what someone with better skills than I could do with this approach.

13. Rogers says:

If only this format is used, I'd go with:
=IFERROR(DATEVALUE(MID(\$B5;SEARCH("??/??/????";\$B5);10));"")

• Chandoo says:

Very interesting. I did not know that SEARCH can find patterns. Thanks for sharing it here 🙂

• Justinas says:

Doesn't work.

• Elias says:

With a little adjustment I think Roger’s approach works great.

=IFERROR(0+MID(B5,SEARCH("??/??/????",B5),10),"")

Regards

• Rogers says:

This was my initial approach:

=WENNFEHLER(DATWERT(TEIL(\$B5;SUCHEN("??/??/????";\$B5);10));"")

• Rogers says:

I used a German Version of Excel 2010 so I had to manually translate the function, but wildcards in SEARCH do work.

See http://office.microsoft.com/en-us/starter-help/search-searchb-functions-HP010342873.aspx?CTT=1
You can use the wildcard characters — the question mark (?) and asterisk (*) — in the find_text argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

• Dolphin Godfred says:

A little tweak ... type "," instead of ";"
Probably just using =MID(\$B5, SEARCH("??/??/????", \$B5, 1), 10) should solve the issue.

-DG

14. Chris Byham says:

I've tried to tackle in a different vein as below:

{=MAX(IFERROR(DATEVALUE(MID(B4,ROW(\$A\$1:\$A\$200),10)),0))}

So, looking at each set of 10 characters (length of date in format mm/dd/yyyy) and trying to use the datevalue function to change the text into an actual date. Then removing the error values using the IFERROR.

Finally, I use the MAX function to obtain the greatest date (using MIN returns the 1st of the month required, as datevalue returns a result for a mm/yyyy string).

The only thing with this formula is when a date is not apparent - in these cases, it seems to return a 0 (and I haven't quite worked out why yet) - I can remove this by using an IF(.....=0,"",.....), but wanted to find a cleaner way - any ideas?

• Chris Byham says:

ps.

This formula should work with additional numbers or / in the text string - if there are two or more dates, it will obviously pick the biggest as using the MAX function.

15. Ben Niebuhr says:

My entry:

=IFERROR(
DATEVALUE(MID(B4,MIN(FIND("0",B4),FIND("1",B4)),10)),
"No date entered!")

I can't believe I didn't think to search for the "/". And now I see Chandoo's challenge about other numbers in the string, I think mine fails.

Rogers gets my vote for best answer so far. I didn't know about the search for patterns either. Awesome tip!

• Rogers says:

Unfortunately SEARCH only uses ? and * as wildcards. Regular expressions would be a really neat improvement

16. Vikas Chauhan says:

I hope this formula works for this table. The formula is as follows:
=DATE(0+MID(B4,FIND("/",B4)+4,4),MID(B4,FIND("/",B4)+1,2),MID(B4,FIND("/",B4)-2,2))

I hope this is useful. The output for this formula is:

When is your date of birth?
DOB

My date of birth is 07/01/1981
1/7/1981

Hmm, 07/01/1981, I think that is when I was born
1/7/1981

Oh wait, was it 07/01/1981? I think so
1/7/1981

07/01/1981 that is when I landed here.
1/7/1981

Date of birth?!? What is that now?
#VALUE!

Let me ask my wife, No wait, I think it is 07/01/1981
1/7/1981

• Vikas Chauhan says:

This formula also works:
=MID(B4,FIND("/",B4)-2,10)

almost same as  Radhish Thekkute suggested.

• Dolphin Godfred says:

Vikas, your function looks great. Need a small tweak though.
Try the date "01/14/1978". I get the output "01-02-1979".
Check the function arguments (click the fx icon before the formula bar). It shows the DATE arguments Year, Month, and Date. It looks like you have given the Month formula in the Date and Date formula in the Month.
Change them and it works like a charm.

-DG

• Vikas Chauhan says:

Thanks Mr. Dolphin Godfred. You are correct.

This looks better:
=DATE(0+MID(B4,FIND("/",B4)+4,4),MID(B4,FIND("/",B4)-2,2),MID(B4,FIND("/",B4)+1,2))

• Swastik says:

How to extract if the dates are as follow :
'9/25/14 4:20 PM
'10/6/14 9:47 PM
'10/6/14 6:52 PM
'10/6/14 2:53 PM
'10/5/14 7:21 PM
'10/5/14 10:29 AM
'10/5/14 10:07 AM
'10/4/14 1:50 PM
'10/4/14 8:28 AM
'10/3/14 7:20 PM
'10/3/14 5:09 PM
'10/3/14 4:49 PM
'10/3/14 4:45 PM
'10/3/14 12:36 PM
'10/2/14 12:46 PM
'10/1/14 3:18 PM
'10/1/14 9:51 AM
'10/12/14 10:07 PM
'10/12/14 7:27 PM
'10/11/14 9:13 PM
'10/11/14 12:05 AM
'10/10/14 4:41 PM
'10/10/14 4:31 PM
'10/10/14 12:43 AM

• Michael Pennington says:

Quickest way to convert to dates in your situation is to copy a blank cell, then paste special and add. Then reformat the cells as dates. This GIF shows the process: http://i.imgur.com/obQmGcx.gif

• Hui... says:

@Swastik
I would use the Data, Text To Column menu
Select the data
goto Data, Text to Column
Fixed Width
Click in the window so that there is a vertical line just after the first ' and a second one just before 4:20
Next
Select each column and adjust the number format
The date format should be MDY
Change the Destination to suit

I Try... work fine:
Spanish version...

=SI.ERROR(FECHANUMERO(EXTRAE(B4,HALLAR("/",B4,1)-2,10)),"No hay DOB")

Regards.

Luis Fdo.

18. Vanita says:

=IF(ISERROR(MID(B4,FIND("/",B4)-2,10)),"No date",DATEVALUE(MID(B4,FIND("/",B4)-2,10)))

=MID(B4,SEARCH("/",B4)-2,10)

20. As simple as
=RegExFind(B4;"\d\d/\d\d/\d\d\d\d")
!!!

This add-on allows to use aadditional worksheet functions like the one above
as well as to search via Ctrl+F dialog using regex wildcards.

21. Eduardo says:

Em portugues  - criei uma formula no gerenciador de nomes
=SEERRO(PROCURAR("/";'extract-dates'!B4);0)

=SE(formula1;EXT.TEXTO(B4;formula1+1;2)&"/"&EXT.TEXTO(B4;formula1-2;2)&"/"&EXT.TEXTO(B4;formula1+4;4);"")

abs
Eduardo Chamon - Brasil

22. Em gerenciador de nomes criei uma fórmula

=SEERRO(PROCURAR("/";'extract-dates'!B4);0)

=SE(formula1;EXT.TEXTO(B4;formula1+1;2)&"/"&EXT.TEXTO(B4;formula1-2;2)&"/"&EXT.TEXTO(B4;formula1+4;4);"")

23. David says:

=IF(ISERROR(MID(B4,SEARCH("??/??/????",B4,1),10)),"No Date",MID(B4,SEARCH("??/??/????",B4,1),10))

24. Andy says:

Here is what I got:=IFERROR(MID(B4,FIND("/",B4)-2,10),"No Date")

25. josh says:

I couldn't help but think of this in terms of the "getNumber" public function that I learned on here not too long ago.  By putting this as a public function, I could reuse it at will and not have to remember the formula.  Unfortunately, I'm no VBA expert.  But I think someone one here could use this idea and modify the code into something better.

Public Function getdate2(fromThis As Range) As String
'Extract the date from a cell and return it.
Dim retVal As String
Dim ltr As String, i As Integer, datecheck As Boolean

retVal = ""
getdate2 = ""
datecheck = False

On Error GoTo last
'Check if the range contains a date
If fromThis.Value Like "*/*/*" Then
datecheck = True
ElseIf fromThis.Value Like "*-*-*" Then
datecheck = True
End If

For i = 1 To Len(fromThis)
ltr = Mid(fromThis, i, 1)
If IsNumeric(ltr) Then
retVal = retVal & ltr
ElseIf ltr = "/" And datecheck Then
retVal = retVal & ltr
ElseIf ltr = "-" And datecheck Then
retVal = retVal & "-"
End If
Next i
getdate2 = Format(retVal, "mm/dd/yyyy")
last:
End Function

26. Esteban says:

Same as the last post

=SI.ERROR(FECHANUMERO(EXTRAE(B4;ENCONTRAR("/";B4;1)-2;10));"")

Esteban

27. cALi says:

Similar but longer than Luis's solution:
=IFERROR(DATE(MID(B4,FIND("/",B4)+4,4),MID(B4,FIND("/",B4)+1,2),MID(B4,FIND("/",B4)-2,2)),"No Date!")

En español:
=SI.ERROR(FECHA(EXTRAE(B4,ENCONTRAR("/",B4)+4,4),EXTRAE(B4,ENCONTRAR("/",B4)+1,2),EXTRAE(B4,ENCONTRAR("/",B4)-2,2)),"No hay fecha!")

28. Eduardo Raupp Schardosim says:

=DIREITA(ESQUERDA(B4;LOCALIZAR("/";B4)+7);10)

29. Fred C says:

=IF(ISERROR(DATE(MID(B9,FIND("/",B9)+4,4),MID(B9,FIND("/",B9)-2,2),MID(B9,FIND("/",B9)+1,2))),"",DATE(MID(B9,FIND("/",B9)+4,4),MID(B9,FIND("/",B9)-2,2),MID(B9,FIND("/",B9)+1,2)))

30. Unal says:

=IFERROR(MID(B4,SEARCH("0",B4,1),10),"no date in here :)")

31. Shrivallabha says:

A] Following entered as ARRAY FORMULA will work:
=MID(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4,1),LEN(B4)+1)),10)
will give erroneous results in case of non-date numbers and will leave blank if there is no number.

B] This non-array formula should have worked (cell formatted as date) but it fails when a character other than space is placed next to it.
=LOOKUP(9.99E+307,TRIM(MID(SUBSTITUTE(B4," ",REPT(" ",100)),(ROW(\$A\$1:\$A\$20)-1)*100+1,100))+0)

32. Bryan says:

I would do something like this:  {=MID(B3,MATCH(TRUE,ISNUMBER(--MID(B3,ROW(INDIRECT("\$1:\$"&LEN(B3))),1)),0),COUNT(--MID(B3,ROW(INDIRECT("\$1:\$"&LEN(B3))),1))+2)}.

This would allow for different formats of the date and different seperators like / - ,.

33. Isti says:

As i m going through the given file my simplest formula is as below.

=MID(B4,FIND("/",B4)-2,10)

not think more.

may be there should be more solution.

Take a cup of coffee guyssssss..

regards
isti

=IFERROR(MID(B8,SEARCH("/",B8,1)-2,10),"NA")

35. Jeet Singh says:

=IFERROR(MID(B4,MIN(IFERROR(FIND({0;1;2;3;4;5;6;7;8;9;"/"},B4),"")),10),"")

• Dolphin Godfred says:

Jeet, that's a good one. And, easy to remember, kind-of.

- DG

36. Michael Royalty says:

I learned about the Mid function from this homework, and also a better use of wildcards with the search function!
I've simplified it to this: =IFERROR(DATEVALUE(MID(B4,SEARCH("??/??/????",B4),10)),"No Date Found")

37. Jitendra says:

Here is the formula,

=IF(ISERROR(FIND("/",B4,1)),"",MID(B4,(FIND("/",B4,1)-2),2)&"/"&MID(B4,(FIND("/",B4,1)+1),2)&MID(B4,(FIND("/",B4,1)+3),5))

38. Brian says:

=IF(ISERROR(MID(B9,SEARCH("0",B9,1),10))," ",MID(B9,SEARCH("0",B9,1),10))

39. Ramnath Divakaran says:

=IFERROR(MID(B4,FIND("/",B4,1)-2,10),"No Date Found")

40. Don Hopkins says:

=IF(ISERR(SEARCH("??/??/????",B4)),"No valid date format found",MID(B4,SEARCH("??/??/????",B4),10))

41. Lewis Kirby says:

Some very interesting replies, and I have already learnt a lot, but none of them seem satisfactory to me, and many simply fail because of my locale -- I am in the UK, so any solution using DATEVALUE will assume that the input format is DD/MM/YYYY and hence fail in locales using that structure as their standard, as will any that coerce the result into a date (eg using "0+").

I also assumed that the homework required a date (ie a serial date) as opposed to simple text, but I could be wrong. (My assumption here is reinforced by the cells being already formatted by Chandoo as Date).

Finally, I assumed that the formula would have to cope with other strings containing "/", eg "I said /what/?", so I was looking for a formula containing "??/??/????" (I would have preferred a tighter constraint but as noted elsewhere such pattern matching is not available in Excel formulae).

Anyway, my offering is
=IFERROR(DATE(MID(B5,6+SEARCH("??/??/????",\$B5),4),MID(B5,SEARCH("??/??/????",\$B5),2),MID(B5,3+SEARCH("??/??/????",\$B5),2)),"no date")

It only allows dates in the MM/DD/YYYY format, it returns a date (as opposed to a string that looks like a date), and it works in any locale (I think -- I can't check!).

I am hoping there is a shorter formula that meets my assumptions.

42. Lewis Kirby says:

Sorry, formula chopped up a bit. It is, on two lines, as follows:

=IFERROR(DATE(MID(B5,6+SEARCH(“??/??/????”,\$B5),4),MID(B5,SEARCH(“??/??/????”,\$B5),2),
MID(B5,3+SEARCH(“??/??/????”,\$B5),2)),”no date”)

43. Rob Easton says:

{=IFERROR(DATEVALUE(MID(B4,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},B4),99)),10)),"n/a")} works

• Lewis Kirby says:

Sorry,  this fails in the UK since DATEVALUE for me assumes the date is DD/MM/YYYY.

44. Vaf? says:

{=IFERROR(MID(B4;MATCH("B*";IFERROR("B"&DATEVALUE(MID(B4;ROW(\$A\$1:\$A\$500);10));"");0);10);"N.A.")}

Supposed text has not more than 500 characters.

• Lewis Kirby says:

Sorry,  again this fails in the UK since DATEVALUE for me assumes the date is DD/MM/YYYY.

45. Doosha says:

A more challenging question is to select all dates within a cell given a specific date format dd/mm/yyyy for example. I can do it with a UDF, but that's cheating.
E.g.
"Let me ask my wife, No wait, I think it is 07/01/1981 or 09/01/198"
Returns
"07/01/1981, 09/01/1981"

46. Bermir says:

=IFERROR(MID(B4,SEARCH("??/??/????",B4),10),"")

This is the same as Rogers' elegant solution but slightly shorter, omitting the DATEVALUE, which does not seem necessary...

• Bermir says:

=IFERROR(MID(B4,SEARCH(“??/??/????”,B4),10),”")*1

Same as before but the added "*1" turns it into a number data type...

• Lewis Kirby says:

Sorry, but again this does not work in my locale -- it says the answer is 7 January 1981.

• Bermir says:

Yes, I tried but in the end came up with the same formula as the one you originally posted using DATE. If there is a shorter one, I'd like to see it too...

47. Bermir says:

Sorry, this last one should have been:

=IFERROR(MID(B4,SEARCH(“??/??/????”,B4),10)*1,””)

48. nizar ahamed khan says:

sir,
simply i used right, mid, mid, left, right(i used first row ),right, formulas in each row in colume C,
thank you sir,
nizar ahamed khan.k

49. Mohammed Mustafa says:

Hi,

Here is my solution....
IFERROR(MID(B4,FIND("/",B4)-2,10),"No Dates here")

Mohammed Mustafa

50. Rosco says:

Thanks Chandoo,

Mine would be:

=IFERROR(- -(MID(B4,FIND("/",B4)-2,10)),"Not Available")

🙂

• Rosco says:

Should be double negative before MID

(Edit by Hui) I added a space for you

• Lewis Kirby says:

I'll stop replying soon, but: sorry, but again this does not work in my locale — it says the answer is 7 January 1981.

51. SUNIL says:

When is your date of birth?
DOB

My date of birth is 07/01/1981
07/01/1981

Hmm, 07/01/1981, I think that is when I was born
Hmm, 07/01/1981

Oh wait, was it 07/01/1981? I think so
ait, was it 07/01/19

07/01/1981 that is when I landed here.
07/01/1981

Date of birth?!? What is that now?

Let me ask my wife, No wait, I think it is 07/01/1981
07/01/1981

52. Kuldeep says:

=IFERROR(MID(B9,(SEARCH("/",B9)-2),10),"-")

53. Manick says:

Assuming that the date format is always dd/mm/yyyy, I used this formula:

=IFERROR(MID(B4,FIND("/",B4)-2,10),"")

• Lewis Kirby says:

But the question explicitly says that the format is MM/DD/YYYY

54. RAHUL AGGARWAL says:

=IFERROR(MID(B4,FIND(0,B4,1),10),"")

55. Shweta Jain says:

=IFERROR(MID(B4,SEARCH("??/??/????",B4),10),"")

• Ahmed says:

I think this is the best formula...

• Lewis Kirby says:

I agree, if we want a textual result that looks like a date.

• Kyle McGhee says:

You just need to use the VALUE() function to turn it into a 'real' date.

56. Senthilkumar RM says:

=MID(B9,(FIND("/",B9)-2),10)

57. Rajinikanth says:

{=MID(B4,MATCH(TRUE,ISNUMBER(1*MID(B4,ROW(\$1:\$255),1)),0),10)}

58. Rajinikanth says:

=MID(B7,SEARCH("??/??/????",B7),10)

59. K Mohan says:

=IFERROR(MID(B4,FIND("/",B4,1)-2,10),"No date in the cell")

60. Yogesh says:

61. ran says:

Assuming for any type of number :

Start position of a numeric  from left

{=MATCH(1,--ISNUMBER(MID(B4,ROW(\$A\$1:\$A\$2000),1)+0),0)}

End Position of  numeric from Right :

{=MATCH(1,--ISNUMBER(LEFT(RIGHT(B4,ROW(\$A\$1:\$A\$2000)),1)+0),0)}

above two coupled with mid and len command should give the desired result

62. vishwa says:

Hi
=IFERROR(MID(B4,FIND("/",B4)-2,10),"There is no date in this Text")

63. Imran says:

=MID(B1, SEARCH("??/??/????", B1, 1), 10)
or
=DATEVALUE(MID(B1, SEARCH("??/??/????", B1, 1), 10))

• Lewis Kirby says:

Okay, I’ll stop replying after this one but: sorry, but again the second solution does not work in my locale — it says the answer is 7 January 1981. This will happen for any formula that uses DATEVALUE or coerces a numeric into a date. To make it work internationally the solution has to explicitly mark the three parts of the string as Month, Day, Year.

Your first solution works well if the answer can be text that looks like a date, but you cannot do any arithmetic on it (well, no arithmetic that works in every locale).

Signing off now 😉

64. ZORRO2005 says:

Hello from Russia:
=LOOKUP(9^9,MID(B4,ROW(INDIRECT(“1:”&LEN(B4)-9)),10)*1)

65. Deepanshu says:

=IFERROR(MID(B4,FIND("/",B4,1)-2,10),"No DOB present")

66. Fred L. says:

Not sure this exact formula has been found but if not here it is:

=MID(B3,FIND(CHAR(47),B3)-2,10)

67. Kiev says:

I would like to use this one, in case there is any empty cell in the text ring,

MID(TRIM(CLEAN(B4)),FIND("/",TRIM(CLEAN(B4)))-2,10)

68. Manisha Nanda says:

1. convert to lowercase
lower(A2)
2. =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(B2,"",""),"a",""),"b",""),"c",""),"d",""),"e",""),"f", ""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""), "p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y", ""),"z",""),"?",""),"!",""),",","")

A lengthy substitution though...!

But it works ..:)
Thanks Chandoo!!

69. Jitendra says:

It should be like this :

*\$B4 - the cell containing text & date mixture.

• Jitendra says:

70. Robert Clark says:

=IF(ISERROR(FIND("/",B4)),"No date found",DATE(MID(B4,SEARCH("??/??/????",B4)+6,4),MID(B4,SEARCH("??/??/????",B4),2),MID(B4,SEARCH("??/??/????",B4)+3,2)))

I think that this may overcome Lewis's comments about date format/regions (seems to work that if teh datestring is 06/31/1981, it converts is to 1st July.

• Lewis Kirby says:

Robert, Thanks, yes this gets around the regions/locale problems I have been highlighting, and is similar to the one I posted a few days ago, plus it actually delivers a date as opposed to a string that looks like a date.

• Kyle McGhee says:

Lewis
=VALUE(TEXT(VALUE(MID(B4, SEARCH("??/??/????", B4, 1), 10)),"mm/dd/yyyy"))

fyi, to make any string date into a value for arithmetic, you can wrap the text with the Value() function.
Use the Text function to shift the MM & DD around.

• Lewis Kirby says:

That's clever -- it only swaps the MM and DD around if they need to be swapped! I knew about using VALUE(), but using TEXT() like that leaves the MM and DD as they are if you are in the USA (etc) but swaps them if you are in the UK.

You just need to wrap it in IFERROR to cater for missing or invalid dates and you're done.

My only nit-pick is that it relies on the Short Date format being DD/MM/YYYY or MM/DD/YYYY. I once worked on a client's machine where their short format was YYYY-MM-DD and this solution would not work for them (whereas solutions mentioned above using the DATE function to pick out the three components of date would -- as opposed to relying on default date formatting). But your solution will work for the vast majority of cases -- thank you!

71. Philip says:

=IFERROR((LEFT((RIGHT(B4,(LEN(B4)-(FIND("/",B4)-3)))),10)),"")

72. Aaron says:

Following the advice of extracting numbers from text:

=IFERROR(MID(B4;MIN(IFERROR(FIND(valD;B4);""));SUMPRODUCT(COUNTIF(valD;MID(B4;ROW(\$A\$1:\$A\$200);1))));"")

where valD = {1,2,3,4,5,6,7,8,9,0,"/"}

73. Prasanna venkatesh says:

=IFERROR(MID(B4,FIND("/",B4)-2,10),"")

74. Pablo says:

Hi Chandoo,
While these formulas work great extracting dates formatted, I have a bigger date challenge where there is no format. The file contains over 10000 lines and the date formats are as many as 1000. For example your 07/01/1981 can be typed like this:
1981 July first, 1 Jul 81, July 1 81, 81 7 1, 81 1 7, 81-1-7, 1981-Jul-01, 1981/07/01, 1981, Jul, July, etc. You got the idea. By the way the dates are between 2010 and Aug 2012.
My approach was to create a table with as many combinations I found. Then I wrote a VBA application that populates an emptly column with the 1st match of the table date. Is not 100% accurate but works most of the time.
Is there a better solution?
Thanks,
Pablo

75. Oli says:

=DATEVALUE(MID(\$B4,MIN(IFERROR(FIND(CHAR(ROW(\$48:\$57)),\$B4),"")),10))

76. Uriel says:

Hello from Mexico!!

=DATEVALUE(IFERROR(MID(B4,FIND("/",B4,1)-2,10),".."))

77. Michael Pennington says:

I had the same thought as Shadow Jam and after a bit of trial and error, I found a way to do it with the Code function.  It isn't pretty, but it is working for me.  Of course, it is coming back with text, so it would require a bit more work if you wanted to use the dates and I didn't put the if error around it, but that would get rid of the NAs.

=MID(B4,MATCH(1,(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))<58)*(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))>47),0),10)

Basically, just looking for a code greater than 47 and less than 58 (ANSI between 0 and 9), finding the first occurrence and returning 10 characters from the beginning of that instance.  Array formula and it isn't very elegant, but it gets the job done.

• Michael Pennington says:

Just couldn't leave it alone.  This gets rid of the NAs and converts it to a usable date:

=IFERROR(DATEVALUE(MID(B4,MATCH(1,(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))<58)*(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))>47),0),10)),"")

Although, this means the date would have to be in the format as shown--i.e. MM/DD/YYYY.

• Lewis Kirby says:

Sorry, I cannot resist -- your use of DATEVALUE() also assumes that the default system date format is MM/DD/YYYY, so on my PC in the UK, the /result/ of your formula is 7th January 1981.

• Michael Pennington says:

Good catch, Lewis.  I guess I should have stuck with my first instinct to use the Date function:

=IFERROR(DATE(RIGHT(MID(B4,MATCH(1,(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))<58)*(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))>47),0),10),4),LEFT(MID(B4,MATCH(1,(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))<58)*(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))>47),0),10),2),MID(MID(B4,MATCH(1,(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))<58)*(CODE(MID(B4,ROW(\$A\$1:\$A\$100),LEN(B4)))>47),0),10),4,2)),"")

Kind of turns it into a monster formula, though!

78. dwi budi says:

my solution is:
=IF(ISERR(MID(B4;FIND("/";B4;1)-2;10)*1);"";MID(B4;FIND("/";B4;1)-2;10)*1)

79. Chris says:

Uh...
I have a solution and it is likely quite similar to some posted above.
However, I use Excel on a German PC, so I only have the German formula names.
is there an easy way to find out the English equivalents?
Cheers,
Chris

• Henk Blanksma says:

I have a sheet with all(?) commands translated in 7 languages, even Russian.
If you you provide me with you  emailadres, I'll send it to you.
Perhaps Chandoo can publish it on his site.

80. Matt Knight says:

Simply:
=+IFERROR(VALUE(MID(B4,FIND("/",B4)-2,10)),"No date entered"), on the assumption that the input is always mm/dd/yyyy rater than /yy

81. =IFERROR(MID(B4,IFERROR(SEARCH("0",B4),SEARCH("1",B4)),10),"")

82. Matt says:

=iferror(LEFT(RIGHT(B4,LEN(B4)-FIND("/",B4)+3),10),"No DOB found")

83. ET says:

=IFERROR(VALUE(MID(B8,SEARCH("??/??/????",B8),10)),"")

84. PB says:

=IF(LEN(B4)-LEN(SUBSTITUTE(B4,"/",""))=2,MID(B4,FIND("/",B4)-2,10),"No Date Found")

85. Roseus says:

=IFERROR(EDATE(LEFT(RIGHT(B4,LEN(B4)-(SEARCH("??/??/????",B4)-1)),10),0),"No Date Present")

Edate validates it as a seemingly possible date, and catches entries like:

- Hope it doesn't re/tu/rn this entry.
- What is 20/50?

• Lewis Kirby says:

Returns 7th January 1981 on my PC, sorry.

86. Kaushik Ranganathan says:

=IF(ISERROR(MID(B4,SEARCH("??/??/????",B4,1),10)),"",TEXT(MID(B4, SEARCH("??/??/????",B4,1), 10),"MM/DD/YYYY"))

Using the TEXT function to convert the format so to "MM/DD/YYYY" and I guess this ensures that the date is displayed in the cell based on the date format on the system. 🙂

87. Brice says:

Can you use these type of string functions when evaluating a cell that has a formula?  For instance I have a cell that has a function in it that is linked to another workbook (=SUMIFS('K:\CF Marketing\Month_End\Brice\FI Interface\[FI_Interface_20 Aug12.xlsx]L043 Backup'!\$N:\$N) and I would like to extract the file name out of the formula.  Would that be possible using functions or would I need to use VB?

• Michael Pennington says:

I have tried to extract a string from a formula without VBA and have yet to find a way to do it.  Over in the MrExcel forums, I found a good bit of code that is pretty clean and easy to use, though: http://bit.ly/O8GtFi

Function CellFormula(Rng As Range) As String
CellFormula = Rng.Formula
End Function

I find it useful whenever I am dealing with poorly designed spreadsheets with useful numbers or text manually keyed into formulas.

88. Weow says:

=MID(B4,FIND("/",B4)-2,10)

89. Ekaterina says:

=IF(IFERROR(FIND("/";B4;1);0)=0;"no date";LEFT(RIGHT(B4;LEN(B4)-IFERROR(FIND("/";B4;1);0)+3);10)) - just another way to extract date 🙂

90. Henk Blanksma says:

I found it more by accident.
So I can't say it my merit.
It is sooo shamefull simple:

=SEARCH(0,B4)
and it came up with the dates!!!

91. Andreas says:

=MID(A2;SEARCH("/";A2)-2;10)

92. Pete says:

=IF(ISERROR(MID(B9,FIND("/",B9)-2,10)),"07/01/1981",MID(B9,FIND("/",B9)-2,10))

93. Nandan Menon says:

I find this as the simplest formula for this purpose:
IFERROR(DATEVALUE(MID(A2,FIND("/",A2)-2,10)),"No Date here!!!")

Thank you

94. Edwin says:

LOL,

long... but it gets the job done!

AS TEXT
=IF(ISERROR(LEFT(RIGHT(LEFT(B4,FIND("/",B4,1)),3),2)&"/"&LEFT(RIGHT(B4,(LEN(B4)-FIND("/",B4,1))),7)), "No DOB", LEFT(RIGHT(LEFT(B4,FIND("/",B4,1)),3),2)&"/"&LEFT(RIGHT(B4,(LEN(B4)-FIND("/",B4,1))),7))

AS VALUE
=IF(ISERROR(LEFT(RIGHT(LEFT(B4,FIND("/",B4,1)),3),2)&"/"&LEFT(RIGHT(B4,(LEN(B4)-FIND("/",B4,1))),7)), "No DOB", VALUE(LEFT(RIGHT(LEFT(B4,FIND("/",B4,1)),3),2)&"/"&LEFT(RIGHT(B4,(LEN(B4)-FIND("/",B4,1))),7)))

95. Edwin says:

=IF(ISERROR(LEFT(RIGHT(LEFT(B4,FIND("/",B4,1)),3),2)&"/"&LEFT(RIGHT(B4,(LEN(B4)-FIND("/",B4,1))),7)), "No DOB", LEFT(RIGHT(LEFT(B4,FIND("/",B4,1)),3),2)&"/"&LEFT(RIGHT(B4,(LEN(B4)-FIND("/",B4,1))),7))

96. edwin says:

All,

Please note that this website will convert your quotes… meaning when you type in a formula, you’re using Char(34). This website will convert your comment into char(147) and Char(148).

So if you copy and paste any formula from this board, make sure you convert the quotes back to the excel standard.

97. Assume that you have the data in in column A. insert the forumula in column B. but i have not used array formula.

=IFERROR(LEFT(RIGHT(A4,LEN(A4)-(FIND("/",A4)-3)),10),"-")

98. samtheman says:

=IFERROR(MID(B4,FIND(0,B4),10),"N/A")

99. aaa says:

=IFERROR(TEXT(MID(B4,MATCH(1,ISNUMBER(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)+0)*1,0),10),"dddd,mmmm dd,yyyy"),"No date")

100. Rajesh says:

=MID(B4,FIND("/",B4,1)-2,10)

101. Ishu says:

=iferror(VALUE(MID(B4,MATCH(TRUE,ISNUMBER(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)*1),0),SUM(--(ISNUMBER(MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)*1))))),"")

CTRL+SHIFT+ENTER

102. Bharath.ch says:

As simple as

=IFERROR(LEFT(RIGHT(B6,((LEN(B6)-FIND("/",B6,1))+3)),10)," Date Not Available")

103. Arnab Sen says:

=IFERROR(MID(B4,FIND("/",B4)-2,10),"No Date Found")

104. Rahim Zulfiqar Ali says:

how to rewrite a word in excel, for example in Cell A1: RAHIM i have to change it like MIHAR
is there any technique in excel?

105. Chirayu says:

There is one big issue Chandoo

Search function cannot use an Array as far as I know.

I have made both find & search function for the criteria below:
----------------------------------------------
my date of birth is 05/03/1989
my date of birth is 05/03/89
my date of birth is 05-03-1989
my date of birth is 05-03-89
my date of birth is 05/03/1989 & I like it
my date of birth is 05/03/89 & I like it
my date of birth is 05-03-1989 & I like it
my date of birth is 05-03-89 & I like it
----------------------------------------------

Find:
=IFERROR(IFERROR(IFERROR(MID(A2,FIND("/",A2,1)-2,10)*1,MID(A2,FIND("/",A2,1)-2,8)*1),MID(A2,FIND("-",A2,1)-2,10)*1),MID(A2,FIND("-",A2,1)-2,8)*1)

Search:
=IFERROR(IFERROR(IFERROR(MID(A2,SEARCH("??/??/??",A2,1),10)*1,MID(A2,SEARCH("??/??/????",A2,1),8)*1),MID(A2,SEARCH("??-??-??",A2,1),10)*1),MID(A2,SEARCH("??-??-????",A2,1),8)*1)

However how would we tackle these variations:

05-Mar-1989
05-Mar-89

106. Korpo says:

Hi all,
I know it is old one but have someone of you tried all those functions against a cell with just a date? like "07/01/1981"? or "07-01-1981" my excel shows all the time error. Just do not see the ??/??/???? pattern.
My spaghetti solution:
=IF(ISNUMBER(IFERROR(DATEVALUE(MID(\$A2,IFERROR(SEARCH("??/??/????",\$A2,1),0)+IFERROR(SEARCH("??-??-????",\$A2,1),0),10)),A2))=FALSE,"text",IFERROR(DATEVALUE(MID(\$A2,IFERROR(SEARCH("??/??/????",\$A2,1),0)+IFERROR(SEARCH("??-??-????",\$A2,1),0),10)),A2))

107. Syedali says:

=IF(COUNT(SEARCH("07/01/1981",B8)),"07/01/1981","")

Any answer to this from Chandoo? 😀

• Hui... says:

You will see simple formulas which work in some but not all cases
You will also see more complex formulas that work in all cases

109. SUMIT CHANDRA says:

Try it...working properly....
=IFERROR(MID(B2,FIND("/",B2,1)-2,10),"")

110. SUMIT CHANDRA says:

Try it...working properly....
=IFERROR(MID(B4,FIND("/",B4,1)-2,10),"")

111. NARSING RAO K says:

sir,

How to convert Date of birth in excel sheet
exp: A1:12/12/1966 in A2 Twelveth Dcecmber nineteen sixty six

112. SYED ALI says:

=IFERROR(MID(B4,SEARCH("/",B4)-2,10),"N/A")

113. Vijay says:

=RIGHT(LEFT(A1,FIND("07/01/1981",A1,1)+9),10)

114. Tim A says:

I thought I would play around with a different solution... using left, right, Len and Find. I also use the date value to ensure that what is returned gets translated into the numeric date value (Excel hates text in it's date fields!)

=IFERROR(DATEVALUE(LEFT(RIGHT(B4,(LEN(B4)-(FIND("/",B4)-3))),10)),"")

115. Rushabh Gala says:

I found out by this formula
correct me if anything can go wrong in data with some different formula
=IFERROR(VALUE(MID(B4,(SEARCH("/",B4,1)-2),10)),0)

116. Scuzzbopper says:

I'm trying a different version of this problem. I have a cell with a series of dates that are not separated like this:

05/04/201605/05/201605/05/201605/10/201605/12/201605/03/201605/12/201605/13/201605/16/201605/18/201605/19/201605/25/201605/25/201605/26/201605/27/2016

I would like to find the oldest date in the list. I feel like I'm close with this formula:

=MIN(MID(A4,FIND("/",A4,1)-2,10))

But the formula is not complete as it just returns the first date in the cell. I'm not sure how to write the rest of the formula so it sort of "loops" through the cell and finds the oldest date.

Any suggestions?

117. Luke Doughty says:

I know there is an easier way but this is how i did it.

118. hailey parker says:

Good discussion - I was fascinated by the info , Does someone know if I could obtain a sample UK VAF1D example to use ?

119. Sasha Money says:

What if you have the following data in column A:

A
1 | January 1 is first day of the year
2 | January 2 is second day of the year
3 | January 4 is fourth day of the year

and in column B you want to return the following:

B
1 | January 1
2 | January 2
3 | January 4

Q1. How do you search or lookup the date within the text string and return the date in column B?

Q2. Say the data for Column B is in a separate spreadsheet from the data in Column A. Is it possible to VLOOKUP the data (Lookup value: January 1) from the table array (January 1 is first day of the year) and if January 1 is in the text string, return "January 1 is first day of the year."

Quite complex.

120. Yves S says:

=IFERROR(MID(B4,SEARCH("??/??/????",B4,1),10),"")
or
=IFERROR(MID(B4,FIND("/",B4,1)-2,10),"")

SEARCH allows wildcards such as "*" or "?"; FIND does not.

121. Sohan says:

=DATE(MID(B8,FIND("/",B8)-2+6,4),MID(B8,FIND("/",B8)-2+3,2),MID(B8,FIND("/",B8)-2,2))

 One race, Every medalist ever – Interactive Excel Visualization Growing a Money Mustache using Excel [for fun]