Extract names from text

Question

This is an example of the data contained in one cell:

2014/08/19 12:59 John Doe add [e-mail]

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

My idea is to find the position ":" and add 4, thus obtaining 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

This works even for spaces with names:

 Function ExtractName(str As String) As String Dim i As Long Dim splitStr() As String Dim nameParts() As String splitStr = Split(str, " ") ReDim nameParts(LBound(splitStr) To UBound(splitStr) - 4) For i = LBound(nameParts) To UBound(nameParts) nameParts(i) = splitStr(i + 2) Next i ExtractName = Join(nameParts, " ") End Function 

What this does effectively is to remove four substrings: date, time, add bit and email address. Everything else in the middle is considered part of the name.

Usage example:

 Debug.Print ExtractName("2014/08/19 12:59 John Doe add [e-mail] ") Debug.Print ExtractName("2014/08/19 12:59 Johan Sebastian Bach add [e-mail] ") Debug.Print ExtractName("2014/08/19 12:59 Fuh Wei Guo Tang add [e-mail] ") Debug.Print ExtractName("2014/08/19 12:59 Jens von dem Hagen add [e-mail] ") Debug.Print ExtractName("2014/08/19 12:59 José Manuel de Santiago Itthuralde add [e-mail] ") 

EDIT Now you say that your input line is split into two lines ... This works for me with the input you specify:

 Function ExtractName(str As String) As String Dim i As Long Dim splitStr() As String Dim nameParts() As String splitStr = Split(Split(str, vbLf)(0), " ") ReDim nameParts(LBound(splitStr) To UBound(splitStr) - 2) For i = LBound(nameParts) To UBound(nameParts) nameParts(i) = splitStr(i + 2) Next i ExtractName = Join(nameParts, " ") End Function 

Answer 2

Here is one liner to achieve what you want.

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

EDIT:

In fact, you do not need VBA for this. You can also use Excel formulas

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

Answer 3

 sampleData = "2014/08/19 12:59 John Doe add [e-mail] " New_String = Split(sampleData) sName = New_String(2) & " " & New_String(3) Debug.Print sName 

Just like that :)

Answer 4

This is true:

 Option Explicit Public Sub Playground() Const SampleData As String = "2014/08/19 12:59 John Doe add [e-mail] " Dim Parts() As String Dim FirstName As String Dim LastName As String Parts = Split(SampleData) FirstName = Parts(2) LastName = Parts(3) Debug.Print FirstName Debug.Print LastName End Sub 

For more complex cases (e.g. name spaces), you may need to modify it a bit.

Answer 5

This will give you the first name (assuming there is only 1), lastname (all other names) and the email address in the options array

 Option Explicit Public Function Name(source As String) As Variant Dim breakup As Variant breakup = Split(source, ":") breakup = Split(Mid(breakup(1), 4), " ") Dim i As Integer Dim FirstName As String FirstName = breakup(0) Dim LastName As String For i = 1 To UBound(breakup) - 2 LastName = LastName & " " & breakup(i) Next LastName = Mid(LastName, 2) Dim Email As String Email = breakup(UBound(breakup)) Name = Array(FirstName, LastName, Email) End Function