CONCATENATE but skip the blanks

  • Thread starter Thread starter TAJ Simmons
  • Start date Start date
T

TAJ Simmons

Hi,

Excel 2002/XP

I'm currently using the Concatenate function combine some data into one cell

Sample Data set 1:
A1=Technology
B1=Computer
C1=PC
D1=Keyboard

Sample Data set 2:
A2=Technology
B2=Computer
C2=Apple
D2=

I'm trying to get (in one cell) (like categories on a website)
Technology > Computer > PC > Keyboard

Which works with this function
=CONCATENATE(A1,">",B1,">",C1,">",D1)

But if any of the individual cells are empty (sample data set 2)

I get this...(Which is not what I want)
Technology > Computer > Apple >

I would like to not have the ">" included if the next cell is empty/blank
This is what I would like to end up with

Technology > Computer > Apple

Cheers
TAJ Simmons
microsoft powerpoint mvp

awesome - powerpoint backgrounds,
free powerpoint templates, tutorials, hints and tips etc
http://www.powerpointbackgrounds.com
 
Thanks Frank.

It Works a treat. The formula is basically saying......IF the data in B1 is not blank, then put in a ">" symbol and the
value from B1, if the data in B1 is blank, then don't put any data in. Then repeat for C, D etc.

You guys are so fast in this excel newsgroup

Cheers
TAJ
 
If each value in that list is a single word (or no spaces), how about:

=SUBSTITUTE(TRIM(A1&" "&A2&" "&A3&" "&A4)," ",">")
 
Dave - Unfortunately the words can sometimes be two or more words...

Thanks Anyway.

TAJ
 
Back
Top