extract date from string in excel

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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

Then in your In your worksheet cell:

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

For Date: =Mid( A1, FindNumeric(A1), 10)