| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF-ISTEXT formula | Excel Discussion (Misc queries) | |||
| ISTEXT TRUE then copy formula from cell X | Excel Worksheet Functions | |||
| Concatenate question | Excel Worksheet Functions | |||
| ISTEXT | Excel Discussion (Misc queries) | |||
| I need help with a =sum IF ISTEXT formula. I keep getting 0. | Excel Discussion (Misc queries) | |||
Linear Mode
