How to remove spaces from a cell that displays the results of multiple vlookup functions

[srkelley]

I'm using Excel 2010 on Windows XP. I wish that I could upload the document, but unfortunately it uses lots of sensitive data. Due to the nature of my position, I cannot upload any files from my computer so making a general document and uploading it isn't possible either.


Below is a copy of the function that is in use. It works, it searchs through multiple columns of data on another tab and displays all of it within one cell. The problem is that I ended up with at least one set of commas if any of the referenced fields don't have data. I'm just trying to have Excel return the data in a way that doesn't return the commas and space for an empty field.

=VLOOKUP(B24,ADATA!$A$2:$AE$20,21)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,22)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,23)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,24)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,25)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,26)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,27)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,28)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,29)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,30)&", "&VLOOKUP(B24,ADATA!$A$2:$AE$20,31)

I found a possible solution here: [URL]http://www.mrexcel.com/forum/excel-questions/603509-multiple-vlookups-one-formula.html#post2990069[/URL].

I'm having a small bit of difficulty in understanding how the solution works and how I should apply it to the above code. Could someone help me please?



I use this function to return the type of access a user may need based upon their role. It saves a lot of time to do it this way since I'm copying the data from Excel to other programs. Some users have multiple roles or other variables that will make their access unique from someone in a similar position (which is why I just don't make separate lists for each role and copy them).

 

[Snakehips]

Then it sounds like you are copying not only spaces but other hidden characters.

Try... =VLOOKUP(TRIM(CLEAN($A10)),'CATEGORY BY PART'!$A:K,5,FALSE)

If that fails then there is one last thing you could but try the above first.

 

[Hongos]

Nope, same, only works if I manually write it down. =(
What we do, is copy from an email the list of CATEGORY and paste it in excel and we use Text to columns to organize and get only the CATEGORY part I need then the part we paste it in that collum, but it always has spaces.

 

[Snakehips]

Last throw of my dice then is.....


=VLOOKUP(TRIM(CLEAN(SUBSTITUTE($A10,CHAR(160),""))),'CATEGORY BY PART'!$A:K,5,FALSE)

 

[Hongos]

Last throw of my dice then is.....


=VLOOKUP(TRIM(CLEAN(SUBSTITUTE($A10,CHAR(160),""))),'CATEGORY BY PART'!$A:K,5,FALSE)

Your brilliant Snakehips! 3rd times the charm. =D