| 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
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 |
|
#5
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 |
|
#6
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 |
|
#7
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 |
|
#8
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 |
|
#9
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 |
|
#10
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 |
|
#11
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 |
|
#12
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 |
|
#13
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 |
| 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) | |||
Hybrid Mode
