IF(ISTEXT Concatenate formula question
=IF(AND(ISTEXT(V3),W3<""),V3&";",V3)&IF(AND(ISTEX T(W3),X3<""),W3&";",W3)&IF(AND(ISTEXT(X3),Y3<"") ,X3&";",X3)&IF(AND(ISTEXT(Y3),Z3<""),Y3&";",Y3)&I F(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
--
Hth
Kassie Kasselman
Change xxx to hotmail
"SCrowley" wrote:
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.
--
Thank you,
scrowley(AT)littleonline.com
"Kassie" wrote:
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.
--
Hth
Kassie Kasselman
Change xxx to hotmail
"SCrowley" wrote:
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!
--
Thank you,
scrowley(AT)littleonline.com
|