=Concatenate

  • Thread starter Thread starter yovation
  • Start date Start date
Y

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
 
Hi David,

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

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

HTH
Martin
 
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)," ",", ")
 
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.
 
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
 
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.)
 
Good catch. I didn't even think of spaces within the cells and so far I'm
gettin' nowhere trying to fix that.
 
Good catch. I didn't even think of spaces within the cells and so far I'm
gettin' nowhere trying to fix that.
Click to expand...

This seems to work....

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

Rick
 
And now try it with 30 columns <bg>


Peo


Rick Rothstein (MVP - VB) said:
This seems to work....

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

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

Rick
 
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
 
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
 
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
 
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.
 
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
Click to expand...

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
 
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
 
Rick Rothstein (MVP - VB) said:
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
Click to expand...

Not there yet.

A1 = Let's see

Result = Lets, see
 
Back
Top