vlookup and returning part of the data

Posted on 2011-05-02
Medium Priority
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 32

Expert Comment

ID: 35510083
Suggest you delete this question and repost in the Excel zone.

Accepted Solution

jstubing earned 1000 total points
ID: 35514786
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)

=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

Expert Comment

ID: 35517281
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

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Preface I don't like visual development tools that are supposed to write a program for me. Even if it is Xcode and I can use Interface Builder. Yes, it is a perfect tool and has helped me a lot, mainly, in the beginning, when my programs were small…
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 while-loops in the C programming language.

839 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