Selective vlookups

marley · May 2nd, 2002, 04:30 PM

I have a workbook that is made up of several work sheets, one of which is a customer list. The last work sheet is a form letter that is generated from varying info from the other sheets. My problem... I need to personalize the form letter. How do I grab only the last name of the customer out of Column J on the Customer List sheet. for example, the customer's name is John Doe. I need the letter to begin, " Dear Mr. Doe, ". I give up.
Thank you in advance.
Marley

Aladin Akyurek · May 2nd, 2002, 04:39 PM

Try:

=TRIM(REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),""))

Requires that a space precedes the last name.

Dave Patton · May 2nd, 2002, 04:42 PM

consider using a free column or insert a column and extract the last name in your customer list

=RIGHT(J5,LEN(J5)-FIND(" ",J5))

Then use a regular Vlookup (revise columns etc as required)

=VLOOKUP(A2,A5:K5,11,0)

Aladin Akyurek · May 2nd, 2002, 04:47 PM

Quote:

On 2002-05-02 15:42, Dave Patton wrote:

consider using a free column or insert a column and extract the last name in your customer list

=RIGHT(J5,LEN(J5)-FIND(" ",J5))

Then use a regular Vlookup (revise columns etc as required)

=VLOOKUP(A2,A5:K5,11,0)

Dave,

I know mine is lengthy. But it will also get

Mr John E. Doe

etc.

Aladin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 2nd, 2002, 04:30 PM	#1
marley Board Regular Join Date: Mar 2002 Location: tucson Posts: 94	I have a workbook that is made up of several work sheets, one of which is a customer list. The last work sheet is a form letter that is generated from varying info from the other sheets. My problem... I need to personalize the form letter. How do I grab only the last name of the customer out of Column J on the Customer List sheet. for example, the customer's name is John Doe. I need the letter to begin, " Dear Mr. Doe, ". I give up. Thank you in advance. Marley

May 2nd, 2002, 04:39 PM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	Try: =TRIM(REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),"")) Requires that a space precedes the last name.

May 2nd, 2002, 04:42 PM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	consider using a free column or insert a column and extract the last name in your customer list =RIGHT(J5,LEN(J5)-FIND(" ",J5)) Then use a regular Vlookup (revise columns etc as required) =VLOOKUP(A2,A5:K5,11,0)