vlookup and returning part of the data

Posted on 2011-05-02
Last Modified: 2012-06-21
I have a table (data) with lots of data exported from outlook soft, one of the column is customer details (refer column C)
      A      B      C
1      100            customer: john smith
2      101            customer: samantha blake
3      102            customer: jo O'conald

on another sheet, I only want to have the information I need and one of it is to seperate the customer first name and last name in two columns.  so I use
this formula returns 'john', but as you can see the next name is longer and it only returns 'sama' instead of 'samantha'.  what formula can I use to pick up name
              A                   B
1       John                Smith
2      Samantha        Blake
3         Jo                  O'connald

please advise?
Question by:joks_73
    LVL 31

    Expert Comment

    Suggest you delete this question and repost in the Excel zone.
    LVL 2

    Accepted Solution

    Here you go.  G2 in my formula is the item number (1,2,3...).   $A$2:$C$4  is the table containing the data.  See the attachement.

    =LEFT(RIGHT(VLOOKUP(G2,$A$2:$C$4,3),LEN(VLOOKUP(G2,$A$2:$C$4,3))-10),FIND(" ",RIGHT(VLOOKUP(G2,$A$2:$C$4,3),LEN(VLOOKUP(G2,$A$2:$C$4,3))-10))-1)

    For LAST_NAME:
    =RIGHT(RIGHT(VLOOKUP(G2,$A$2:$C$4,3),LEN(VLOOKUP(G2,$A$2:$C$4,3))-10),LEN(VLOOKUP(G2,A2:C4,3))-10-FIND(" ",RIGHT(VLOOKUP(G2,$A$2:$C$4,3),LEN(VLOOKUP(G2,$A$2:$C$4,3))-10)))


    Jeff Vlookup-And-Returning-Data.xlsx
    LVL 6

    Expert Comment

    You're asking two questions that are interfering with each other.

    The last question is how to split a space-delimited string into two columns.
    If you attempt to split the string at the same time as the VLookup, you will have to use VLookup multiple times in the same query, which has performance impacts on large amounts of data.
    I suggest changing your query to just bring back the result in one hidden column, then parse the result into two different columns.

    If "customer: john smith" is in cell A1, this will retrieve the first name:
    =LEFT(RIGHT(A1,(LEN(A1)-FIND(": ", A1)-1)), FIND(" ",RIGHT(A1,(LEN(A1)-FIND(": ", A1)-1))))

    ...and this will retrieve the last name:
    =RIGHT(RIGHT(A1,(LEN(A1)-FIND(": ", A1)-1)),LEN(RIGHT(A1,(LEN(A1)-FIND(": ", A1)-1)))-FIND(" ", RIGHT(A1,(LEN(A1)-FIND(": ", A1)-1))))

    Another technique might be to parse the data when it is loaded to split on the colon and the space, so the fields will be separate from the beginning.

    Author Closing Comment

    Thank you so much, the solution was easy to follow, specially with an attachment as well.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    An Outlet in Cocoa is a persistent reference to a GUI control; it connects a property (a variable) to a control.  For example, it is common to create an Outlet for the text field GUI control and change the text that appears in this field via that Ou…
    This tutorial is posted by Aaron Wojnowski, administrator at  To view more iPhone tutorials, visit This is a very simple tutorial on finding the user's current location easily. In this tutorial, you will learn ho…
    The goal of this video is to provide viewers with basic examples to understand and use structures in the C programming language.
    Video by: Grant
    The goal of this video is to provide viewers with basic examples to understand and use nested-loops in the C programming language.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now