Concatenate

[sburkhar]

I'm using the concatenate funtion successfully, however, the cells that are not being used are returning "N/A. Is there a way to have the unused cells remain blank? This sheet populates another sheet and it's going to be time consuming to go thru and remove all of them. I'm trying to take steps out of the process, and I seem to add one step for every 2 I take out.

 

[sburkhar]

I'm sorry - it's returning #N/A

 

[StillLearningExcel]

What are you trying to concatenate can you not just A1&A2?

 

[MrKowz]

I'm sorry - it's returning #N/A

What is your formula? Can you please provide some data, as well as your desired outcome?

 

[sburkhar]

column B = 72231234
column A = Battery Terminal
Result I'm looking for: 72231234 Battery Terminal

My result is fine. The unused cells are what's causing the problem.
I'm setting up an automated template for the analysts to use here and in Mexico, so I need it to be as clear as possible.

 

[MrKowz]

What do you mean by unused cells? If the cells are blank, then when used in concatenate, they just simply won't show up.

What is your formula? Also, give us an example where it is returning #N/A!.

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Battery Terminal</TD><TD style="TEXT-ALIGN: right">72231234</TD><TD>72231234 Battery Terminal</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>Battery Terminal</TD><TD style="TEXT-ALIGN: right"></TD><TD>Battery Terminal</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">72231234</TD><TD style="TEXT-ALIGN: right">72231234 </TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet2</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C1</TH><TD style="TEXT-ALIGN: left">=CONCATENATE(B1," ",A1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[sburkhar]

I gotcha. The concatenate information is pulled from a sheet that is returning results from a Vlookup. Those cells DO contain #N/A.

So now the question becomes, is there a way to have a Vlookup NOT return #N/A if it does not find a match?

 

[MrKowz]

Yes, and that is the ideal way to fix this.

In those VLOOKUP cells, you need to use an error handler:

Excel 2003 or older:

=IF(ISERROR(*your vlookup*),"",*your vlookup*)

Excel 2007 or newer:

=IFERROR(*your vlookup*,"")

 

[sburkhar]

Here's my formula. Can you re-write it with the IFERROR? I can't get it.

=VLOOKUP(E29,'[VLOOKUP.xls]0-39999 (2)'!$B:$C,2,FALSE)

 

[MrKowz]

Try:

=IFERROR(VLOOKUP(E29,'[VLOOKUP.xls]0-39999 (2)'!$B:$C,2,FALSE),"")

Also, I would highly reccomend against using workbook names, sheet names, and variable names that share the same name as a reserved function or variable. It could cause some bugs, and it also makes things harder to decode if there is an issue later.