=Concatenate

[yovation]

Hi,

I am using this formula:
=(A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1)

Problem is, sometimes a few of the cells are blank. I end up getting
2 commas , , together.
I would like to add a conditional that would check to see if the cell
is blank. If so, skip the cell.

How would I do that?

Thank you.
David

 

[MartinW]

Hi David,

This doesn't check for blanks beforehand, it cleans them
up afterwards.

=SUBSTITUTE((A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1)," ,","")

HTH
Martin

 

[T. Valko]

Try this:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1)," ",", ")

And, just in case it gets chopped up by line wrap and removes the spaces:

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1
&" "&D1&" "&E1&" "&F1)," ",", ")

 

[T. Valko]

Not quite.

Clear all the cells in the range. The formula returns a comma. Now, enter
something in cell D1. The formula still returns an extra comma.

 

[MartinW]

Yeah Biff, it falls down in a couple of places.
When A1 is blank (very untidy)
When F1 is blank (a little untidy)

I'm glad you came in when you did, I was just starting
to explore a way to trim it but I was starting in the wrong
direction, who knows where I would have ended up.

Regards
Martin

 

[Dave Peterson]

I like this technique--but only if each of the cells does not have any spaces in
it.

(Just a small warning to the OP.)

 

[T. Valko]

Good catch. I didn't even think of spaces within the cells and so far I'm
gettin' nowhere trying to fix that.

 

[Dave Peterson]

You could use substitute() a bunch of times. Not too bad with 6 cells, but
sooner or later, I'd break down and use a UDF like the one at JE McGimpsey's
site:

http://www.mcgimpsey.com/excel/udfs/multicat.html

 

[Rick Rothstein \(MVP - VB\)]

Good catch. I didn't even think of spaces within the cells and so far I'm

gettin' nowhere trying to fix that.

This seems to work....

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"
","|"),","," "))," ",","),"|"," ")

Rick

 

[Peo Sjoblom]

And now try it with 30 columns <bg>


Peo

This seems to work....

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"
","|"),","," "))," ",","),"|"," ")

Rick

 

[Rick Rothstein \(MVP - VB\)]

Thankfully, that wasn't the question that was asked (it was hard enough to
get this one to work).<vbg>

Rick

 

[Rick Rothstein \(MVP - VB\)]

Oh, what the hell... it is only a little more typing...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1&","&G1&","&H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1&","&O1&","&P1&","&Q1&","&R1&","&S1&","&T1&","&U1&","&V1&","&W1&","&X1&","&Y1&","&Z1&","&AA1&","&AB1&","&AC1&","&AD1,"
","|"),","," "))," ",","),"|"," ")

<g>

Rick

 

[Rick Rothstein \(MVP - VB\)]

Not sure why the line got broken up with the blank line between it (it also
broke at a blank space making it hard to see. Here is the "line", broken up
in more manageable lengths. You will have to manually concatenate it in
something like notepad before attempting to paste it into the worksheet...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE
(A1&","&B1&","&C1&","&D1&","&E1&","&F1&","&G1&","
&H1&","&I1&","&J1&","&K1&","&L1&","&M1&","&N1&","&
O1&","&P1&","&Q1&","&R1&","&S1&","&T1&","&U1&","&
V1&","&W1&","&X1&","&Y1&","&Z1&","&AA1&","&AB1&","&
AC1&","&AD1," ","|"),","," "))," ",","),"|"," ")

Rick

 

[MartinW]

No need for notepad, just paste into
the formula bar one line at a time.

Regards
Martin

 

[T. Valko]

Where's the space after each item? <g>

xx, aa, yy, zz

 

[Sandy Mann]

Or just paste it all into the formula bar and then delete the line breaks.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

You will have to manually concatenate it in something like notepad

Select the cell where you want to paste the formula. Then paste the formula
in the *formula bar*. You can then backspace out the line breaks.

 

[Rick Rothstein \(MVP - VB\)]

Good catch. I didn't even think of spaces within the cells and so far

Where's the space after each item? <g>

xx, aa, yy, zz

I didn't think the OP really wanted them.<g>

Actually, those damned space kept getting in the way of everything I tried.
Finally, I got the bright idea to dump them, which ended up making the
parsing easier... then I forgot to put them back in. I just needed to add
one space character into the formula to put them back...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"","|"),",","
"))," ",", "),"|"," ")

Rick

 

[Rick Rothstein \(MVP - VB\)]

Or just paste it all into the formula bar and then delete the line breaks.

Ah, but of course... paste it into the FORMULA BAR, not the cell itself
<imagine the sound of a hand slapping one's forehead here>.

Rick

 

[T. Valko]

I didn't think the OP really wanted them.<g>

Actually, those damned space kept getting in the way of everything I
tried. Finally, I got the bright idea to dump them, which ended up making
the parsing easier... then I forgot to put them back in. I just needed to
add one space character into the formula to put them back...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1&","&B1&","&C1&","&D1&","&E1&","&F1,"","|"),",","
"))," ",", "),"|"," ")

Rick

Not there yet.

A1 = Let's see

Result = Lets, see