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).

 

[Scott Huish]

For each VLOOKUP result, can there be spaces in the result or not?

 

[Snakehips]

srkelly,

Welcome to MrExcel.

As far as I see it, the link you refer to is dealing with the elimination of #N/R errors from a lookup. Your situation appears different in as much as you will likely be guaranteed a good lookup albeit that certain elements will return ‘blank’ and give you an accumulation of ‘, ‘ commas and spaces.


I was looking at it earlier and could only come up with two options.


Firstly and maybe in line with Scott's thinking (?)..........
If you will not have any spaces in the values ‘looked up’ then maybe……..

=SUBSTITUTE(TRIM(SUBSTITUTE(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),",","  "))," ",", ")

Or if there might be spaces in the 'look ups' then maybe……..

[/FONT]


[FONT=Helvetica]=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,21)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,22)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,23)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,24)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,25)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,26)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,27)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,28)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,29)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,30)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,31),” ","~"))," ",", "),"~"," ")[/FONT]


[FONT=Helvetica]

All a bit grizzly I'm afraid.


Fingers crossed there are no silly typos in there, as it is not tested.


Hope that helps.

 

[srkelley]

Thanks for the responses and the welcome.

@Scott Huish; There can be spaces for each result.

@Snakehips; Thanks for the solutions!

The first one definitely killed all of the excessive commas. As you noted, it also placed commas within results that had their own spaces in them.

The second one contained an error.
The following information shows in a box that only has the options of selecting "OK" or "Help":

"The formula you typed contains an error.

-For information about fixing common formula problems, click Help.
-To get assistance in entering a function, click Function Wizard (Formulas tab, function Library group).
-If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (')."

So a standard error box.

B24 is selected with a blue border for its' selection, the type that shows while creating formulas manually sometimes.

The red, slightly larger, bolded part of the code below is what Excel has highlighted in the function bar. It's the ["~"] that has its' attention. Not the last one, but the one before it.

[FONT=Helvetica]=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,21)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,22)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,23)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,24)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,25)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,26)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,27)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,28)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,29)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,30)," ","~")&" "&SUBSTITUTE(VLOOKUP(B24,ADATA!$A$2:$AE$20,31),” ",[SIZE=4][COLOR=#ff0000][B]"~"[/B][/COLOR][/SIZE]))," ",", "),"~"," ")[/FONT]

 

[Snakehips]

sr,

Give this a try...

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

It looks for all the world the same and as far as I can see should be ok. I have altered one double quote " mark that looked as if it were italicised, that's all?????

Please report back.

 

[Hongos]

Hello,
First of all sorry srkelley for interupting on your question.

I have a similar question that Im wondering if any of you can help me with. Might as well since its the same topic put it here.

I have a code not as complicated as the above, =VLOOKUP($A10,'CATEGORY BY PART'!$A:K,5,FALSE)

I wanted to know how I can remove spaces on the result. Spaces before and after. If not mainly after. Since every category I add has spaces, I want to avoid having to backspace every time I paste a list.

Thanks for the help. =)

**Oh and I tried adding &"*" after $A10 which somebody suggested, but doesnt work.

 

[Snakehips]

Hongos,
Welcome to Mr Excel.

That sound as if just need to use TRIM ??

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

Hope that helps.

 

[Hongos]

Hongos,
Welcome to Mr Excel.

That sound as if just need to use TRIM ??

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

Hope that helps.

Thanks for the reply, =)

Nope, didnt work. When I add a category lets say DEMOBAG with space before or after. It gives me a result of #N/A.
Also when I press enter after adding the code, it gives me a pop up window stating to select file and what sheet. Is that normal? Then it changes the code to this: =TRIM(VLOOKUP($A10,'[CATEGORY BY PART]CATEGORY BY PART'!$A:K,5,FALSE))

Pretty much I want to trim only the A10 which is the category I paste. It will pull up the information from a list that doesnt have any extra spaces.

 

[Snakehips]

Then apply the TRIM to A10

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

 

[Hongos]

Then apply the TRIM to A10

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

Okay, turns out it works but it doesnt haha. Let me explain myself. If I add a CATERGORY manually typing it. IF I add many spaces it will trim it and it will bring back my result. But if I paste something that has the spaces already. It doesnt work. It continues to show #N/A..

Could that be some error that cant be fixed? If there is no way around that what would be the best way to trim the whole Colum the fastest and easiest meathod manually?