=Concatenate

[Rick Rothstein \(MVP - VB\)]

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

Not there yet.

A1 = Let's see

Result = Lets, see

????

I just retested it to be sure... that's not what happens on my system.
Besides, I'm not even touching the apostrophe character in my manipulations,
so I don't see how the result you posted could happen.

Rick

 

[T. Valko]

????

I just retested it to be sure... that's not what happens on my system.
Besides, I'm not even touching the apostrophe character in my
manipulations, so I don't see how the result you posted could happen.

Rick

http://img110.imageshack.us/img110/5131/concatcellsrx3.jpg

Hard to tell at the end of the formula what are spaces. I just pasted the
formula into the formula bar and backspaced out the line breaks.

 

[Rick Rothstein \(MVP - VB\)]

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

http://img110.imageshack.us/img110/5131/concatcellsrx3.jpg

Hard to tell at the end of the formula what are spaces. I just pasted the
formula into the formula bar and backspaced out the line breaks.

Which is what I did to retest it. Since your posted sample shows the
apostrophe in the word Let's, I'm guessing you got my formula to work now,
right?

Rick

 

[T. Valko]

Which is what I did to retest it. Since your posted sample shows the
apostrophe in the word Let's, I'm guessing you got my formula to work now,
right?

Rick

I must be missing something here. The screencap shows the result of the
formula as:

Let's, see

The comma is not supposed to be there.

What does the apostrophe have to do with anything?

I'm confused!

 

[Rick Rothstein \(MVP - VB\)]

The comma is not supposed to be there.

What does the apostrophe have to do with anything?

I'm confused!

Your first post said this...

A1 = Let's see

Result = Lets, see

You left out the apostrophe on your Result line and so I ended up
concentrating on that omission instead of what you were actually saying.

I guess it is back to the drawing boards on my formula. <g>

Rick

 

[T. Valko]

Your first post said this...

A1 = Let's see

Result = Lets, see

You left out the apostrophe on your Result line and so I ended up
concentrating on that omission instead of what you were actually saying.

I guess it is back to the drawing boards on my formula. <g>

Rick
A1 = Let's see
Result = Lets, see

Oh, I see! <g>

So I'm the source of the confusion. Well, it's not the first time that's
happened!

Good luck on figuring this out (without ending up with a monster formula).

 

[Rick Rothstein \(MVP - VB\)]

Good luck on figuring this out

One of my space characters got lost somewhere... I put it back and I think
the formula work as it was (with that one space character added back in).

(without ending up with a monster formula).

I don't think so. As I said, I believe my original formula (plus the "lost
space") works. Here is the file purposely broken up to avoid losing any
spaces again...

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

Rick

 

[T. Valko]

One of my space characters got lost somewhere... I put it back and I think
the formula work as it was (with that one space character added back in).



I don't think so. As I said, I believe my original formula (plus the "lost
space") works. Here is the file purposely broken up to avoid losing any
spaces again...

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

Rick

Ok, nice goin'!

I finally got it to work.

One thing I did notice is that if a cell contains something like: 10, 10

then it adds another space: 10, 10.

But, if the cells do contain commas then it would make sense to use a
different delimiter.

For example:

A1 = 10, 10
B1 = 10

Result = 10, 10, 10

you could TRIM out the extra space but it's impossible to distinguish if the
result came from 1, 2 or 3 cells.

10, 10, 10

So in this case a different delimiter should be used.

Let's put this puppy to rest!

 

[Harlan Grove]

I finally got it to work.

One thing I did notice is that if a cell contains something like:
10, 10

then it adds another space: 10, 10.

But, if the cells do contain commas then it would make sense to use
a different delimiter.

For example:

A1 = 10, 10
B1 = 10

Result = 10, 10, 10

you could TRIM out the extra space but it's impossible to
distinguish if the result came from 1, 2 or 3 cells.

10, 10, 10

So in this case a different delimiter should be used.

Let's put this puppy to rest!

....

Oh, ye of little endurance! Hundreds of messages have been written on
this topic in many different programming language ngs.

If the delimiting char could appear in a field, then an alternative
would be delimiting the field, say, with double quotes. At that point
it becomes expedient to process each field before concatenating it to
the others. As in this monster.

=SUBSTITUTE(SUBSTITUTE(TRIM(
SUBSTITUTE(IF(COUNTIF(A1,"*,*")-COUNTIF(A1,"""*"""),
""""&A1&"""",A1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(B1,"*,*")-COUNTIF(B1,"""*"""),
""""&B1&"""",B1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(C1,"*,*")-COUNTIF(C1,"""*"""),
""""&C1&"""",C1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(D1,"*,*")-COUNTIF(D1,"""*"""),
""""&D1&"""",D1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(E1,"*,*")-COUNTIF(E1,"""*"""),
""""&E1&"""",E1)," ",CHAR(127)))," ",", "),CHAR(127)," ")

 

[Rick Rothstein \(MVP - VB\)]

Oh, ye of little endurance! Hundreds of messages have been written on

this topic in many different programming language ngs.

If the delimiting char could appear in a field, then an alternative
would be delimiting the field, say, with double quotes. At that point
it becomes expedient to process each field before concatenating it to
the others. As in this monster.

=SUBSTITUTE(SUBSTITUTE(TRIM(
SUBSTITUTE(IF(COUNTIF(A1,"*,*")-COUNTIF(A1,"""*"""),
""""&A1&"""",A1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(B1,"*,*")-COUNTIF(B1,"""*"""),
""""&B1&"""",B1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(C1,"*,*")-COUNTIF(C1,"""*"""),
""""&C1&"""",C1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(D1,"*,*")-COUNTIF(D1,"""*"""),
""""&D1&"""",D1)," ",CHAR(127))&" "&
SUBSTITUTE(IF(COUNTIF(E1,"*,*")-COUNTIF(E1,"""*"""),
""""&E1&"""",E1)," ",CHAR(127)))," ",", "),CHAR(127)," ")

Two things about your formula. One, you stopped one column too soon... the
OP wanted it carried out to Column F. Not a big deal as the extension is
easy enough to do. Two, why are you subtracting off COUNTIF(C1,"""*""") in
your IF conditional tests? I mean, I think I know what you were trying to do
(stop some kind of recursion); but, on a hunch, I removed them all and your
formula worked fine without them. The only other thing you could have done
to shorten you formula is to use CHAR(1) instead of CHAR(127) which saves 14
(assuming you extend your formula for Column F. Here is your formula
reworked for the above comments...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(IF(COUNTIF(A1,"*,*"),""""&A1&"""",A1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(B1,"*,*"),""""&B1&"""",B1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(C1,"*,*"),""""&C1&"""",C1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(D1,"*,*"),""""&D1&"""",D1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(E1,"*,*"),""""&E1&"""",E1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(F1,"*,*"),""""&F1&"""",F1),"
",CHAR(1)))," ",", "),CHAR(1)," ")

As far as I can tell, it seems to work fine.

Rick

 

[T. Valko]

Two things about your formula. One, you stopped one column too soon... the
OP wanted it carried out to Column F. Not a big deal as the extension is
easy enough to do. Two, why are you subtracting off COUNTIF(C1,"""*""") in
your IF conditional tests? I mean, I think I know what you were trying to
do (stop some kind of recursion); but, on a hunch, I removed them all and
your formula worked fine without them. The only other thing you could have
done to shorten you formula is to use CHAR(1) instead of CHAR(127) which
saves 14 (assuming you extend your formula for Column F. Here is your
formula reworked for the above comments...

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(IF(COUNTIF(A1,"*,*"),""""&A1&"""",A1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(B1,"*,*"),""""&B1&"""",B1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(C1,"*,*"),""""&C1&"""",C1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(D1,"*,*"),""""&D1&"""",D1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(E1,"*,*"),""""&E1&"""",E1),"
",CHAR(1))&" "&SUBSTITUTE(IF(COUNTIF(F1,"*,*"),""""&F1&"""",F1),"
",CHAR(1)))," ",", "),CHAR(1)," ")

As far as I can tell, it seems to work fine.

Rick

I was thinking more along the lines of:

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

Where:

A1 = 10, 10
B1 = 10

Then:

10, 10 - 10

Just substitute the "-" for the between cell delimiter. Although I have to
admit that using a dash isn't really visually appealing to me but neither
are the quotes.

Hundreds of messages have been written on
this topic in many different programming language ngs.

I'm sure there have been. This is similar to parsing names/addresses.

 

[Harlan Grove]

. . . Two, why are you subtracting off COUNTIF(C1,"""*""") in your IF
conditional tests? . . .

....

On the assumption that if the field is already double-quote delimited, it
doesn't need bracketing double quotes, but I screwed up the test. My
original test would have added unnecessary bracketing double quotes if the
field were already bracketed by double quotes but contained no commas.
Should have used

COUNTIF(A1,"*,*")*COUNTIF(A1,"<>""*""")

instead.

 

[Bernd P]

Hello David,

Why not just array-enter
=MultiCat(ReturnNonEmpty(A1:F1),",")
?

See
http://www.sulprobil.com/html/concatenate.html
please.

Regards,
Bernd