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" wrote:

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" wrote:

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(IS NUMBER(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&";",I F(ISNUMBER(Y3),"",Y3))&IF(AND(ISTEXT(Z3),AA3<"",I STEXT(AA3)),Z3&";",IF(ISNUMBER(Z3),"",Z3))&IF(AND( ISTEXT(AA3),AB3<"",ISTEXT(AB3)),AA3&";",IF(ISNUMB ER(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


"David Biddulph" wrote:

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.
--
David Biddulph

"Kassie" wrote in message
...
=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