IF(ISTEXT Concatenate formula question
 

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

IF(ISTEXT Concatenate formula question
 

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

IF(ISTEXT Concatenate formula question
 

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,
--
Thank you,

scrowley(AT)littleonline.com


"CLR" wrote:


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

IF(ISTEXT Concatenate formula question
 

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<""),...
--
David Biddulph

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

IF(ISTEXT Concatenate formula question
 

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

Vaya con Dios,
Chuck, CABGx3





"SCrowley" wrote:

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,
--
Thank you,

scrowley(AT)littleonline.com


"CLR" wrote:


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

IF(ISTEXT Concatenate formula question
 

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

IF(ISTEXT Concatenate formula question
 

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

IF(ISTEXT Concatenate formula question
 

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,"")&I F(ISTEXT(AB3),";"&AB3,"")&IF(ISTEXT(AC3),";"&AC3," ")

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

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

Vaya con Dios,
Chuck, CABGx3





"SCrowley" wrote:

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,
--
Thank you,

scrowley(AT)littleonline.com


"CLR" wrote:


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

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

IF(ISTEXT Concatenate formula question
 

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

IF(ISTEXT Concatenate formula question
 

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

--
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

IF(ISTEXT Concatenate formula question
 

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

IF(ISTEXT Concatenate formula question
 

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 ;;;;.


--
Biff
Microsoft Excel MVP


"SCrowley" wrote in message
...
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,
--
Thank you,

scrowley(AT)littleonline.com


"CLR" wrote:


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

IF(ISTEXT Concatenate formula question
 

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

IF(ISTEXT Concatenate formula question
 

Daddylonglegs wrote:

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


"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

IF(ISTEXT Concatenate formula question
 

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