# extract date from string in excel

How can I extract date from such strings in excel?

``````John8/11/12
Jackson09/11/2013
``````

Desired result:

``````John     8/11/12
Jackson  09/11/2013
``````

Can I do this using only excel formula using excel functions?

-

## migrated from stackoverflow.comMar 8 '13 at 4:24

This question came from our site for professional and enthusiast programmers.

Anyone with the same problem but less particular about Excel might prefer copying into Word, Find what: "([A-z])([0-9])", with Use wildcards, Replace with: "\1^t\2" and copy results into Excel. – pnuts Mar 11 '13 at 14:13

To extract name:

``````=LEFT(A1, MATCH(TRUE,ISNUMBER(1*MID(A1,{1,2,3,4,5,6,7,8,9},1)),0)-1)
``````

To extract date:

``````=RIGHT(A1, LEN(A1) - MATCH(TRUE,ISNUMBER(1*MID(A1,{1,2,3,4,5,6,7,8,9},1)),0)+1)
``````

Note : both must be entered as an array formula `CTRL` + `SHIFT` + `ENTER`

-

Use `=LEFT(A1,MIN(FIND(0,SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},0)&0))-1)` in the column directly to the right of the string and `=SUBSTITUTE(A1,B1,"")` as the following column... Then copy the formula down.

-

You might want to use regular expressions in order to achieve this. Im not very familiar with excel but heres a link that might help http://www.dzone.com/snippets/regular-expressions-excel-2007

-

Excel Function:

``````Function FindNumeric( pvsString As String ) As Integer
Dim viPosition As Integer
Dim viFoundAt As Integer
viFoundAt = 0
For viPosition = 1 To Len(pvsString)
If IsNumeric(Mid(pvsString, viPosition, 1)) Then
viFoundAt = viPosition
Exit For
End If
Next viPosition
End Function
``````

For Name: `=Mid( A1, 1, FindNumeric(A1)-1)`
For Date: `=Mid( A1, FindNumeric(A1), 10)`