How to ignore cells while concatenating cells in Excel

[Jenny310]

Hi. I'm trying to concat Cell A, B, and C. However, I don't want to concat the row if column A has "N".
For example, below I would like to skip 4and 5 because column A has "N".


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">Hi. I'm trying to concat Cell A, B, and C. However, I don't want to concat the row if column A has "N".
For example, Below I would like to skip row 4 and 5 because column A has "N". (Highlighted in yellow)

Thanks,
Jenny</body>[TABLE="width: 233"]
<colgroup>







</colgroup><tbody>[TR]
[TD="width: 59"]A[/TD]
[TD="width: 99"]B[/TD]
[TD="width: 69"]C[/TD]
[TD="width: 84"]Concate[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Dodge[/TD]
[TD]c100[/TD]
[TD]yDodgec100[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Cap[/TD]
[TD]c101[/TD]
[TD]yCapc101[/TD]
[/TR]
[TR]
[TD="class: xl65"]n[/TD]
[TD="class: xl65"]Circus[/TD]
[TD="class: xl65"]c102[/TD]
[TD]nCircusc102[/TD]
[/TR]
[TR]
[TD="class: xl65"]n[/TD]
[TD="class: xl65"]Warren[/TD]
[TD="class: xl65"]c103[/TD]
[TD]nWarrenc103[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Tooker[/TD]
[TD]c104[/TD]
[TD]yTookerc104[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Dodge[/TD]
[TD]c100[/TD]
[TD]yDodgec100[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trebor76]

Hi Jenny310,

Welcome to MrExcel!!

Assuming the data starts in Row 1, put this in cell D1...

=IF(A1="n","",A1&B1&C1)

...and copy it down as far as you need it.

HTH

Robert

 

[Jenny310]

Works Great. Thank you Robert.

Hi Jenny310,

Welcome to MrExcel!!

Assuming the data starts in Row 1, put this in cell D1...

=IF(A1="n","",A1&B1&C1)

...and copy it down as far as you need it.

HTH

Robert

 

[Trebor76]

Thanks for letting us know and you're welcome

 

[Jenny310]

Hi Robert, Sorry, one more question.
I added another column.
If column A is "N" or Column D is blank don't concate the row.

[TABLE="width: 380"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]Concate[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Dodge[/TD]
[TD]c100[/TD]
[TD]yard[/TD]
[TD]yDodgec100[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Cap[/TD]
[TD]c101[/TD]
[TD]fence[/TD]
[TD]yCapc101[/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD]Circus[/TD]
[TD]c102[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD]Warren[/TD]
[TD]c103[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Tooker[/TD]
[TD]c104[/TD]
[TD]dog[/TD]
[TD]yTookerc104[/TD]
[/TR]
[TR]
[TD]y[/TD]
[TD]Dodge[/TD]
[TD]c100[/TD]
[TD]fence[/TD]
[TD]yDodgec100[/TD]
[/TR]
</tbody>[/TABLE]

 

[philwojo]

Try this:

[COLOR=#333333]=IF(or(A1="n",D1=""),"",A1&B1&C1)[/COLOR]

 

[Jenny310]

Thank you Phil.