asked on

Pull named out of Cell

We have a column of data all in Column A. In column B I would like it to pull out the First and Last Name. This is always right after the time - which ends in either "am" or "pm". Is there a way to pull out the 2 words after the "am" or "pm"? This would always be the name we need.

Norie

Try this in B1 and copy it down.

=MID(A1,IF(ISNUMBER(SEARCH("am",A1)),SEARCH("am",A1)+3,IF(ISNUMBER(SEARCH("pm",A1)),SEARCH("pm",A1)+3,LEN(A1))),LEN(A1))

Rob Henson

Is it always just the time and First Name Last Name eg:

4:15 pm Fred Bloggs

I suspect you can do it with the text to columns wizard, an upload with some sample data would confirm.

Frank Helk

I admit that my solution is tailored very tight to the example you've given, but nevertheless: Here is it: ...

Function GetName(s As String) As String
    
    Dim token As Variant
    Dim s2 As String
    Dim i As Integer
    
    i = 0
    i = WorksheetFunction.Max(InStr(1, s, " am ") + 4, InStr(1, s, " pm ") + 4)
       
    s2 = Mid(s, i)
    
    For Each token In Split(s2, " ")
    
        If Left(token, 1) = "$" Then Exit For
        GetName = GetName + IIf(GetName = "", "", " ") + token
        
    Next

End Function

Open in new window

Put that Function code into a macro module within your Excel workbook. Then you could use it like this:

(your example texts in column A, function calls in column F)

Hope that helps ...

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial