# Concatenate and IF Formula

#### Ryske

##### New Member
Hi Everyone!
Below is the Row 1 is a commodity and in row 2 is the number of pallets. Anything that more than "0" the commodity will appear in "Output" but with "/" as a separator in each commodity, which I do not know what formula would it be? Thanks in advance.

 BREAD FROZEN WATER FFV TOTAL OUTPUT 2 2 2 5 11 BREAD/FROZEN/WATER/FFV

<tbody>
</tbody>

#### S.H.A.D.O.

##### Well-known Member
Welcome to the board Ryske,

Code:
``=CONCATENATE(IF(A2>0,A2&" BREAD/",""),IF(B2>0,B2&" FROZEN/",""),IF(C2>0,C2&" WATER/",""),IF(D2>0,D2&" FFV",""))``
I hope this helps!

#### Eric W

##### MrExcel MVP
If you have one of the newer versions of Excel (365 or some 2016), you can use the TEXTJOIN:

=TEXTJOIN("/",TRUE,IF(A2:D2>0,A\$1:D\$1,""))

or

=TEXTJOIN("/",TRUE,IF(A2:D2>0,A2:D2&" "&A\$1:D\$1,""))

both confirmed with Control+Shift+Enter.

If not, you can use S.H.A.D.O.'s formula. However, do you care if the result has a trailing "/" ? That will happen if FFV = 0. It would take a longer formula to get around that, something like:

#### Ryske

##### New Member
Thank you S.H.A.D.O., this helps!

#### Ryske

##### New Member
Hello Eric W, thanks, this helps as well. I have used the first "textjoin" formula and it works perfectly.
To answer your question, FFV should not have "/". I have removed the "/" from FFV, the desired output is achieved. Thanks once again!

#### Nishant94

##### Well-known Member
Although you have got your answer, here is my way of doing it. Just few modification to the above answers.

OR

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### Ryske

##### New Member
Thank you Nishant, another formula that I can use in future.

#### S.H.A.D.O.

##### Well-known Member
Thanks for the feedback Ryske, I am glad you have got it sorted.

#### DineshBabu Kondalsamy

##### New Member
You can use both formulas,,

1. =CONCATENATE(IF(A2>0,A2&" BREAD/",""),IF(B2>0,B2&" FROZEN/",""),IF(C2>0,C2&" WATER/",""),IF(D2>0,D2&" FFV",""))

2. =
IF(SUM(A2:D2),SUBSTITUTE(IF(A2>0,"\$A\$1&"/","")&IF(B2>0,"\$B\$1&"/","")&IF(C2>0,"\$C\$1&"/","")&IF(D2>0,"\$D\$1&"/",""),"/","",COUNTIF(A2:D2,">0")),"")

Thanks,
Dinesh Babu K
****** id="cke_pastebin" style="position: absolute; top: 41px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
 =CONCATENATE(IF(A2,\$A\$1&"/",""),IF(B2,\$B\$1&"/",""),IF(C2,\$C\$1&"/",""),IF(D2,\$D\$1&"/",""))

<tbody>
</tbody>
 =CONCATENATE(IF(A2,\$A\$1&"/",""),IF(B2,\$B\$1&"/",""),IF(C2,\$C\$1&"/",""),IF(D2,\$D\$1&"/",""))

<tbody>
</tbody>
 =CONCATENATE(IF(A2,\$A\$1&"/",""),IF(B2,\$B\$1&"/",""),IF(C2,\$C\$1&"/",""),IF(D2,\$D\$1&"/",""))

<tbody>
</tbody>
 =CONCATENATE(IF(A2,\$A\$1&"/",""),IF(B2,\$B\$1&"/",""),IF(C2,\$C\$1&"/",""),IF(D2,\$D\$1&"/",""))

<tbody>
</tbody>
</body>

#### DineshBabu Kondalsamy

##### New Member
Hi,

You can ues both the formulas,

1.

2.
=IF(SUM(A2:D2),SUBSTITUTE(IF(A2>0,\$A\$1&"/","")&IF(B2>0,\$B\$1&"/","")&IF(C2>0,\$C\$1&"/","")&IF(D2>0,\$D\$1&"/",""),"/","",COUNTIF(A2:D2,">0")),"")

Thanks,
Dinesh Babu.

1,082,258
Messages
5,364,090
Members
400,779
Latest member
lumers

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...