Extract names from a text

Question

This is a sample data contained in one cell:

2014/08/19 12:59 John Doe
add [email protected]

I need to extract the name in the text. I know that it is always placed after the datetime stamp.

My idea is to find the position of ":" and add 4 thus getting the position of the first letter of the first name:

colonLoc = InStr(sampleData, ":")
firstLetterLoc = colonLoc + 4

How can I get the first and last name after that?

Answer 1

Here is a one liner to achieve what you want.

debug.print Mid(Split(Split(Range("A1").Value, Chr(10))(0), ":")(1), 3)

EDIT:

Actually you don't need VBA for this. You can use Excel formulas as well

=MID(A1,FIND(":",A1)+3,FIND(CHAR(10),A1)-(FIND(":",A1)+3))