IF(ISTEXT Concatenate formula question

[SCrowley]

Excel 2007

I have a formula that concatenates cells if they have text and inserts ";"
between each text string. However, it also puts multiple ;;; at the end if
there was no text in the last 3 cells. What, if anything can I tweak to
prevent this?

=IF(ISTEXT(V3),V3&";","")&IF(ISTEXT(W3),W3&";","")&IF(ISTEXT(X3),X3&";","")&IF(ISTEXT(Y3),Y3&";","")&IF(ISTEXT(Z32),Z3&";","")&IF(ISTEXT(AA3),AA3&";","")&IF(ISTEXT(AB3),AB3&";","")&IF(ISTEXT(AC3),AC3,"")

Thank you!

 

[CLR]

Could be your "empty" cells actually have a "space" (or other invisible
character) in them, which counts as "text", and gets CONCATENATED.

Vaya con Dios,
Chuck, CABGx3

 

[SCrowley]

Thank you, Chuck. But that doesn't seem to be the problem. I checked the
empty cells to make sure there were no spaces OR non-printing characters.

Gracias,

 

[David Biddulph]

You won't get the ; included unless you have text in the cell to give a TRUE
condition for the ISTEXT() test. I wonder if you have a "" string in the
relevant cells? If so, you may wish to test for that too. One option may
be something like
IF(AND(ISTEXT(Y3),LEN(Y3)>0),... or IF(AND(ISTEXT(Y3),Y3<>""),...

 

[CLR]

Strange, your formula works fine in my Excel 97.......might the cells contain
formulas that evaluate to " "...........

Vaya con Dios,
Chuck, CABGx3

 

[Kassie]

Your formula works exactly as intended? The only problem being that, when
the lsat cell, or cells, do not contain anything, your concatenation will end
on a ";". Eg, if you have touch, feel and smell in the relevant cells, your
result will be touch;feel;smell;. I would have thought that you would prefer
not to have the last ";". On the other hand, it will require quite some
tweaking to eliminate that, and you will have to decide whether that is
essential.

 

[SCrowley]

Kassie,

I do not want the last ";" and the tweaking is what I'm looking for. I'm
currently trying David and CLR's suggestions.

 

[CLR]

Maybe this is what you're after...........

=IF(ISTEXT(V3),V3,"")&IF(ISTEXT(W3),";"&W3,"")&IF(ISTEXT(X3),";"&X3,"")&IF(ISTEXT(Y3),";"&Y3,"")&IF(ISTEXT(Z3),";"&Z3,"")&IF(ISTEXT(AA3),";"&AA3,"")&IF(ISTEXT(AB3),";"&AB3,"")&IF(ISTEXT(AC3),";"&AC3,"")

Vaya con Dios,
Chuck, CABGx3

 

[Kassie]

=IF(AND(ISTEXT(V3),W3<>""),V3&";",V3)&IF(AND(ISTEXT(W3),X3<>""),W3&";",W3)&IF(AND(ISTEXT(X3),Y3<>""),X3&";",X3)&IF(AND(ISTEXT(Y3),Z3<>""),Y3&";",Y3)&IF(AND(ISTEXT(Z3),AA3<>""),Z3&";",Z3)&IF(AND(ISTEXT(AA3),AB3<>""),AA3&";",AA3)&IF(AND(ISTEXT(AB3),AC3<>""),AB3&";",AB3)&IF(ISTEXT(AC3),AC3,"")
should do the trick

 

[David Biddulph]

But if you've got numbers (rather than text) in the cells, that formula will
concatenate them, which presumably wasn't what the OP wanted.

 

[Kassie]

Just goes to show if you are in too much of a rush! I'll work on it!

 

[Kassie]

I said it would take a lot of tweaking, but this one seems to work out fine!

=IF(AND(ISTEXT(V3),W3<>"",ISTEXT(W3)),V3&";",IF(ISNUMBER(V3),"",V3))&IF(AND(ISTEXT(W3),X3<>"",ISTEXT(X3)),W3&";",IF(ISNUMBER(W3),"",W3))&IF(AND(ISTEXT(X3),Y3<>"",ISTEXT(Y3)),X3&";",IF(ISNUMBER(X3),"",X3))&IF(AND(ISTEXT(Y3),Z3<>"",ISTEXT(Z3)),Y3&";",IF(ISNUMBER(Y3),"",Y3))&IF(AND(ISTEXT(Z3),AA3<>"",ISTEXT(AA3)),Z3&";",IF(ISNUMBER(Z3),"",Z3))&IF(AND(ISTEXT(AA3),AB3<>"",ISTEXT(AB3)),AA3&";",IF(ISNUMBER(AA3),"",AA3))&IF(AND(ISTEXT(AB3),AC3<>"",ISTEXT(AC3)),AB3&";",IF(ISNUMBER(AB3),"",AB3))&IF(ISTEXT(AC3),AC3,"")

 

[T. Valko]

The only way you can get a result like ;;;; is if there is *something* in
the cells. Do these cells contain formulas that under certain conditions
return a blank ("") ? If so, then you'll get ;;;;. If the cells truly are
*empty* then there is no way you'll get ;;;;.

 

[daddylonglegs]

Hello scrowley,

Are you specifically concerned with making a distinction between text and
numbers or are you just trying to concatenate cells which contain an entry?

If the latter try

=SUBSTITUTE(IF(V3="","",";"&V3)&IF(W3="","",";"&W3)&IF(X3="","",";"&X3)&IF(Y3="","",";"&Y3)&IF(Z3="","",";"&Z3)&IF(AA3="","",";"&AA3)&IF(AB3="","",";"&AB3)&IF(AC3="","",";"&AC3),";","",1)

or you could shorten this by using MCONCAT function from morefunc add-in
[download here http://xcell05.free.fr/english/]

and use this formula

=SUBSTITUTE(MCONCAT(IF(V3:AC3="","",";"&V3:AC3)),";","",1)

confirmed with CTRL+SHIFT+ENTER

I said it would take a lot of tweaking, but this one seems to work out fine!

=IF(AND(ISTEXT(V3),W3<>"",ISTEXT(W3)),V3&";",IF(ISNUMBER(V3),"",V3))&IF(AND(ISTEXT(W3),X3<>"",ISTEXT(X3)),W3&";",IF(ISNUMBER(W3),"",W3))&IF(AND(ISTEXT(X3),Y3<>"",ISTEXT(Y3)),X3&";",IF(ISNUMBER(X3),"",X3))&IF(AND(ISTEXT(Y3),Z3<>"",ISTEXT(Z3)),Y3&";",IF(ISNUMBER(Y3),"",Y3))&IF(AND(ISTEXT(Z3),AA3<>"",ISTEXT(AA3)),Z3&";",IF(ISNUMBER(Z3),"",Z3))&IF(AND(ISTEXT(AA3),AB3<>"",ISTEXT(AB3)),AA3&";",IF(ISNUMBER(AA3),"",AA3))&IF(AND(ISTEXT(AB3),AC3<>"",ISTEXT(AC3)),AB3&";",IF(ISNUMBER(AB3),"",AB3))&IF(ISTEXT(AC3),AC3,"")

 

[SCrowley]

Are you specifically concerned with making a distinction between text and
numbers or are you just trying to concatenate cells which contain an entry?

Just trying to concatenate cells which contain an entry. Thanks. I'll keep
y'all posted if your solution does the trick.

Many bows of gratitude.
--
Thank you,

scrowley(AT)littleonline.com

Hello scrowley,

Are you specifically concerned with making a distinction between text and
numbers or are you just trying to concatenate cells which contain an entry?

If the latter try

=SUBSTITUTE(IF(V3="","",";"&V3)&IF(W3="","",";"&W3)&IF(X3="","",";"&X3)&IF(Y3="","",";"&Y3)&IF(Z3="","",";"&Z3)&IF(AA3="","",";"&AA3)&IF(AB3="","",";"&AB3)&IF(AC3="","",";"&AC3),";","",1)

or you could shorten this by using MCONCAT function from morefunc add-in
[download here http://xcell05.free.fr/english/]

and use this formula

=SUBSTITUTE(MCONCAT(IF(V3:AC3="","",";"&V3:AC3)),";","",1)

confirmed with CTRL+SHIFT+ENTER

I said it would take a lot of tweaking, but this one seems to work out fine!

=IF(AND(ISTEXT(V3),W3<>"",ISTEXT(W3)),V3&";",IF(ISNUMBER(V3),"",V3))&IF(AND(ISTEXT(W3),X3<>"",ISTEXT(X3)),W3&";",IF(ISNUMBER(W3),"",W3))&IF(AND(ISTEXT(X3),Y3<>"",ISTEXT(Y3)),X3&";",IF(ISNUMBER(X3),"",X3))&IF(AND(ISTEXT(Y3),Z3<>"",ISTEXT(Z3)),Y3&";",IF(ISNUMBER(Y3),"",Y3))&IF(AND(ISTEXT(Z3),AA3<>"",ISTEXT(AA3)),Z3&";",IF(ISNUMBER(Z3),"",Z3))&IF(AND(ISTEXT(AA3),AB3<>"",ISTEXT(AB3)),AA3&";",IF(ISNUMBER(AA3),"",AA3))&IF(AND(ISTEXT(AB3),AC3<>"",ISTEXT(AC3)),AB3&";",IF(ISNUMBER(AB3),"",AB3))&IF(ISTEXT(AC3),AC3,"")
--
Hth

Kassie Kasselman
Change xxx to hotmail

 

[SCrowley]

Viola!

daddylonglegs, THANK YOU!!!! Thank ALL of you! That worked perfectly!

I don't know what I'd do without this awesome MSCommunity resource!
--
Thank you,

scrowley(AT)littleonline.com

Hello scrowley,

Are you specifically concerned with making a distinction between text and
numbers or are you just trying to concatenate cells which contain an entry?

If the latter try

=SUBSTITUTE(IF(V3="","",";"&V3)&IF(W3="","",";"&W3)&IF(X3="","",";"&X3)&IF(Y3="","",";"&Y3)&IF(Z3="","",";"&Z3)&IF(AA3="","",";"&AA3)&IF(AB3="","",";"&AB3)&IF(AC3="","",";"&AC3),";","",1)

or you could shorten this by using MCONCAT function from morefunc add-in
[download here http://xcell05.free.fr/english/]

and use this formula

=SUBSTITUTE(MCONCAT(IF(V3:AC3="","",";"&V3:AC3)),";","",1)

confirmed with CTRL+SHIFT+ENTER

I said it would take a lot of tweaking, but this one seems to work out fine!

=IF(AND(ISTEXT(V3),W3<>"",ISTEXT(W3)),V3&";",IF(ISNUMBER(V3),"",V3))&IF(AND(ISTEXT(W3),X3<>"",ISTEXT(X3)),W3&";",IF(ISNUMBER(W3),"",W3))&IF(AND(ISTEXT(X3),Y3<>"",ISTEXT(Y3)),X3&";",IF(ISNUMBER(X3),"",X3))&IF(AND(ISTEXT(Y3),Z3<>"",ISTEXT(Z3)),Y3&";",IF(ISNUMBER(Y3),"",Y3))&IF(AND(ISTEXT(Z3),AA3<>"",ISTEXT(AA3)),Z3&";",IF(ISNUMBER(Z3),"",Z3))&IF(AND(ISTEXT(AA3),AB3<>"",ISTEXT(AB3)),AA3&";",IF(ISNUMBER(AA3),"",AA3))&IF(AND(ISTEXT(AB3),AC3<>"",ISTEXT(AC3)),AB3&";",IF(ISNUMBER(AB3),"",AB3))&IF(ISTEXT(AC3),AC3,"")
--
Hth

Kassie Kasselman
Change xxx to hotmail