Concatenate cells, ignoring blanks, then trim

dunlopoil · Jul 16, 2008

=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 · Jul 16, 2008

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 · Jul 16, 2008

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

dunlopoil · Jul 16, 2008

MrKowz said:
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 · Jul 16, 2008

mikerickson said:
=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 · Jul 16, 2008

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 · Jul 16, 2008

Try like this

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

mikerickson · Jul 16, 2008

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 · Jul 16, 2008

removed, Mike replied.

dunlopoil · Jul 16, 2008

barry houdini said:
Try like this

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

Thanks Barry and Mike,
That works great.

Concatenate cells, ignoring blanks, then trim

dunlopoil

Board Regular

Excel Facts

MrKowz

Well-known Member

mikerickson

MrExcel MVP

dunlopoil

Board Regular

dunlopoil

Board Regular

MrKowz

Well-known Member

barry houdini

MrExcel MVP

mikerickson

MrExcel MVP

DonkeyOte

MrExcel MVP

dunlopoil

Board Regular

Similar threads

Forum statistics

Share this page

Concatenate cells, ignoring blanks, then trim

Board Regular

Excel Facts

Well-known Member

MrExcel MVP

Board Regular

Board Regular

Well-known Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock