IF(ISTEXT Concatenate formula question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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,
 
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<>""),...
 
Strange, your formula works fine in my Excel 97.......might the cells contain
formulas that evaluate to " "...........

Vaya con Dios,
Chuck, CABGx3
 
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.
 
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.
 
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
 
=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
 
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.
 
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,"")
 
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 ;;;;.
 
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



Kassie said:
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,"")
Click to expand...
 
Daddylonglegs said:
Are you specifically concerned with making a distinction between text and
numbers or are you just trying to concatenate cells which contain an entry?
Click to expand...

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


daddylonglegs said:
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



Kassie said:
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
Click to expand...
Click to expand...
 
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


daddylonglegs said:
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



Kassie said:
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
Click to expand...
Click to expand...
 
Back
Top