Concatenate cells, ignoring blanks, then trim

[dunlopoil]

=IF(Z2="","",Z2&","&IF(AA2="","",AA2&",")&IF(AB2="","",AB2&",")&IF(AC2="","",AC2&","))

Hi,
I'm using the above formula to join text from columns Z through AC, separated by a comma. I now want to remove the comma at the end of the new string. Also, I would prefer it if the four cells were separated by a slash rather than a comma, but when I simply replace the commas in the formula with slashes I get an error.

Can anyone help?

Many thanks,
Doug.

 

[MrKowz]

Replacing ALL the commas with a slash would definately give you errors...

This is how you could replace the commas with a slash, and get rid of the comma(or slash) at the end:

=IF(Z2="","",Z2&"/"&IF(AA2="","",AA2&"/")&IF(AB2="","",AB2&"/")&IF(AC2="","",AC2))

 

[mikerickson]

=substitute(trim(z2&" "&aa2&" "&ab2&" "&ac2)," ","/")

 

[dunlopoil]

Replacing ALL the commas with a slash would definately give you errors...

This is how you could replace the commas with a slash, and get rid of the comma(or slash) at the end:

=IF(Z2="","",Z2&"/"&IF(AA2="","",AA2&"/")&IF(AB2="","",AB2&"/")&IF(AC2="","",AC2))

Thanks Keith. That works as far as replacing commas with slashes but I'm still left with a slash at the end of the string. Any ideas?

 

[dunlopoil]

=substitute(trim(z2&" "&aa2&" "&ab2&" "&ac2)," ","/")

Mike,
Thanks for that. It's not quite what I'm looking for as it puts a slash between each word (e.g. if the entry in Z2 is red apples it appears as red/apples). I want to separate each cell entry, not each word.

 

[MrKowz]

Try this one out

=if(right(IF(Z2="","",Z2&"/"&IF(AA2="","",AA2&"/")&IF(AB2="","",AB2&"/")&IF(AC2="","",AC2)),1)="/",left(IF(Z2="","",Z2&"/"&IF(AA2="","",AA2&"/")&IF(AB2="","",AB2&"/")&IF(AC2="","",AC2)),len(IF(Z2="","",Z2&"/"&IF(AA2="","",AA2&"/")&IF(AB2="","",AB2&"/")&IF(AC2="","",AC2)))-1),IF(Z2="","",Z2&"/"&IF(AA2="","",AA2&"/")&IF(AB2="","",AB2&"/")&IF(AC2="","",AC2)))

It checks to see if the last character is a "/"... if it is, it gives you everything up to that "/"

 

[barry houdini]

Try like this

=SUBSTITUTE(IF(Z2="","","/"&Z2)&IF(AA2="","","/"&AA2)&IF(AB2="","","/"&AB2)&IF(AC2="","","/"&AC2),"/","",1)

 

[mikerickson]

This is messy, but it should work
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(Z2," ",CHAR(5))&" "&SUBSTITUTE(AA2," ",CHAR(5))&" "&SUBSTITUTE(AB2," ",CHAR(5))&" "&SUBSTITUTE(AC2," ",CHAR(5)))," ","/"),CHAR(5)," ")

After simulposting: Barry's is much cleaner.

 

[DonkeyOte]

removed, Mike replied.

 

[dunlopoil]

Try like this

=SUBSTITUTE(IF(Z2="","","/"&Z2)&IF(AA2="","","/"&AA2)&IF(AB2="","","/"&AB2)&IF(AC2="","","/"&AC2),"/","",1)

Thanks Barry and Mike,
That works great.