Cut length of Vlookup results

[Factotum]

Would anyone know how to return only the first several digits of a vlookup value? I'm trying to save space by shrinking the columns and only having a portion of the lookup value.

For example, my source data shows the table below. I would like to just pull in "Jones, Indiana". I've tried variations of LEN and TRIM nested with the VLOOKUP, but can't seem to get it right. Thanks for any ideas!

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee ID[/TD]
[TD]Employee Name[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Jones, Indiana Junior the Fourth Esquire[/TD]
[/TR]
</tbody>[/TABLE]

 

[ajamess]

Not sure if they are all formatted the same. But this would give you everything before the 2nd space.

=LEFT(B2,FIND(" ", B2, FIND(" ", B2)+1))

With some testing it appears to give me the correct result. However, every B2 would need to be your vlookup.

Or you could Vlookup into B, use the above formula in C, and hide B.

 

[philwojo]

Or if you only want say the 1st 10 or 15 characters of your vlookup results, just surround the vlookup with the LEFT function, like this:

=left(vlookup(a1,a1:b10,2,0),15)

That would do whatever your vlookup is and then trim it down to only the 1st 15 characters that come back.
Obviously put in your vlookup in place of my code there and change the 15 to whatever you want it to be.

 

[Scott T]

If you want to just return the first X number of characters then this will work. Change the 15 to the number of

=TRIM(LEFT(VLOOKUP(A9,A2:B3,2,0),15))

 

[Factotum]

Thank you philwojo!

That was such a painfully obvious solution, I'm embarrassed that I posted it! I'm always using =vlookup(left....) but I never thought to use =left(vlookup...). Thank you so much for your help!

 

[ajamess]

Oh I'd assumed that wasn't working because of varying lengths of different names and needed something more intricate. Glad you got it figured out though

 

[Scott T]

If it will always be in that format last name, first name (space after first name) then this will pull just the last and first name regardless of length

Excel 2010
	A	B
1	Employee ID	Employee Name
2	123456	Jones, Indiana Junior the Fourth Esquire
3	13498799	Green, Beth the farmer
4	7	Loooooooooongname, firsttttttsname the thrid
5
6
7
8
9	123456	Jones, Indiana
10	7	Loooooooooongname, firsttttttsname
11	13498799	Green, Beth
iRFQ

Cell Formulas
Range	Formula
B9	=TRIM(LEFT(VLOOKUP(A9,A2:B4,2,0),SEARCH(" ",VLOOKUP(A9,A2:B4,2,0),SEARCH(",",VLOOKUP(A9,A2:B4,2,0))+2)))
B10	=TRIM(LEFT(VLOOKUP(A10,A2:B4,2,0),SEARCH(" ",VLOOKUP(A10,A2:B4,2,0),SEARCH(",",VLOOKUP(A10,A2:B4,2,0))+2)))
B11	=TRIM(LEFT(VLOOKUP(A11,A2:B4,2,0),SEARCH(" ",VLOOKUP(A11,A2:B4,2,0),SEARCH(",",VLOOKUP(A11,A2:B4,2,0))+2)))