Using IF and Concatenate Function

[charlesav]

Hi Guys,

Hope someone can help me, been thinking and trying this formula for a while but can't seem to make it work. I have a file wherein I need to concatenate different rows and will ignore blank rows using if function so it will look neat. I used different "IF" functions but I can't seem to make it work. Hope someone can help me. I've attached a workbook and attached a picture just in case. Thanks in advance.

Untitled.png

[Pete_UK]

What formula are you using? The attachment just shows the results of the formula.

You could use SUBSTITUTE to get rid of the unwanted space-commas, like this:

=SUBSTITUTE(your_existing_formula," ,","")

Hope this helps.

Pete

[charlesav]

What formula are you using? The attachment just shows the results of the formula.

You could use SUBSTITUTE to get rid of the unwanted space-commas, like this:

=SUBSTITUTE(your_existing_formula," ,","")

Hope this helps.

Pete

Hi Pete,

Thanks for the reply. I've updated the attached Excel file for you to see the formula I'm using. Kind of hard to explain if I take a picture. I need to fix the formula highlighted in Yellow, seems like "substitute" is not the correct formula for it. But let me know if you want me to take another picture. Thanks.

[Sam Capricci]

Pete's suggestion (as I interpret it) would be this...
=SUBSTITUTE(IF(ISTEXT(@F1:F8)=1,CONCATENATE(F1,", ",F2,", ",F3,", ",F4,", ",F5,", ",F6,", ",F7,", ",F8),CONCATENATE(F1,", ",F2,", ",F3,", ",F4,", ",F5,", ",F6,", ",F7,", ",F8))," ,","")
but it does leave you a trailing comma in your example.
Now, how many of these do you have at any time? Because the number of them as you have it in your formula will require you to make continual adjustments as the column gets longer.
But if you only have a reasonable limit, this if then statement would work.

=IF(AND(F1<>"",F2=""),F1,IF(AND(F1<>"",F2<>"",F3=""),F1&", "&F2,IF(AND(F1<>"",F2<>"",F3<>"",F4=""),F1&", "&F2&", "&F3,IF(AND(F1<>"",F2<>"",F3<>"",F4<>"",F5=""),F1&", "&F2&", "&F3&", "&F4,IF(AND(F1<>"",F2<>"",F3<>"",F4<>"",F5<>"",F6=""),F1&", "&F2&", "&F3&", "&F4&", "&F5,IF(AND(F1<>"",F2<>"",F3<>"",F4<>"",F5<>"",F6<>"",F7=""),F1&", "&F2&", "&F3&", "&F4&", "&F5&", "&F6,IF(AND(F1<>"",F2<>"",F3<>"",F4<>"",F5<>"",F6<>"",F7<>"",F8=""),F1&", "&F2&", "&F3&", "&F4&", "&F5&", "&F6&", "&F7,IF(AND(F1<>"",F2<>"",F3<>"",F4<>"",F5<>"",F6<>"",F7<>"",F8<>""),F1&", "&F2&", "&F3&", "&F4&", "&F5&", "&F6&", "&F7&", "&F8,""))))))))

[Pete_UK]

It always helps to see an appropriate sample workbook.

You can simplify the formula in F9 to this:

=SUBSTITUTE(TRIM(CONCATENATE(F1," ",F2," ",F3," ",F4," ",F5," ",F6," ",F7," ",F8))," ",", ")

Note that I am only adding a space after each term in the CONCATENATE function, and the TRIM function around this will remove any multiple and leading/trailing spaces. The SUBSTITUTE function then changes any remaining spaces to comma-space.

Hope this helps.

Pete

[charlesav]

It always helps to see an appropriate sample workbook.

You can simplify the formula in F9 to this:

=SUBSTITUTE(TRIM(CONCATENATE(F1," ",F2," ",F3," ",F4," ",F5," ",F6," ",F7," ",F8))," ",", ")

Note that I am only adding a space after each term in the CONCATENATE function, and the TRIM function around this will remove any multiple and leading/trailing spaces. The SUBSTITUTE function then changes any remaining spaces to comma-space.

Hope this helps.

Pete

Exactly what I've been looking for. Thank you so much for the help!!

[Pete_UK]

You're welcome - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete