Spaces
Hi
Anyone can help I have this problem in this formula A B C D 1 Olives Chili Cumin 2 12 19 15 =IF(A2<20;$A$1;"")&IF(B2<20;$B$1;"")&IF(C2<20;$C$1 ;"") What I get is Oiveschilicumin without spaces in between, how to have the spaces in this formula Thanks in advance Jam |
Spaces
Hi Jam,
=IF(A2<20;$A$1;"")&" "&IF(B2<20;$B$1;"")&" "&IF(C2<20;$C$1;"") -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message oups.com... | Hi | Anyone can help | I have this problem in this formula | A B C D | 1 Olives Chili Cumin | 2 12 19 15 | =IF(A2<20;$A$1;"")&IF(B2<20;$B$1;"")&IF(C2<20;$C$1 ;"") | What I get is Oiveschilicumin without spaces in between, how to have | the spaces in this formula | Thanks in advance | Jam | |
Spaces
=IF(A2<20;$A$1;"")&" "&IF(B2<20;$B$1;"")&" "&IF(C2<20;$C$1;"")
-- David Biddulph wrote in message oups.com... Hi Anyone can help I have this problem in this formula A B C D 1 Olives Chili Cumin 2 12 19 15 =IF(A2<20;$A$1;"")&IF(B2<20;$B$1;"")&IF(C2<20;$C$1 ;"") What I get is Oiveschilicumin without spaces in between, how to have the spaces in this formula Thanks in advance Jam |
Spaces
Just a little point you may want to run trim over it afterwards to remove
duplicate spaces for example: =trim(IF(A2<20;$A$1;"")&" "&IF(B2<20;$B$1;"")&" "&IF(C2<20;$C$1;"")) if B2=20 you get Olives _ _ Cumin and therefore the trim will remove duplicate spaces as give you Olives _ Cumin -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Niek Otten" wrote: Hi Jam, =IF(A2<20;$A$1;"")&" "&IF(B2<20;$B$1;"")&" "&IF(C2<20;$C$1;"") -- Kind regards, Niek Otten Microsoft MVP - Excel wrote in message oups.com... | Hi | Anyone can help | I have this problem in this formula | A B C D | 1 Olives Chili Cumin | 2 12 19 15 | =IF(A2<20;$A$1;"")&IF(B2<20;$B$1;"")&IF(C2<20;$C$1 ;"") | What I get is Oiveschilicumin without spaces in between, how to have | the spaces in this formula | Thanks in advance | Jam | |
Spaces
Niek Otten, Martin Fishlock, and David Biddulph all use
"")&" "& (why???) you could use this instead " ")& To eliminate many spaces as the result . The header should have a space in front than OP could use the original formula. " wrote: Hi Anyone can help I have this problem in this formula A B C D 1 Olives Chili Cumin 2 12 19 15 =IF(A2<20;$A$1;"")&IF(B2<20;$B$1;"")&IF(C2<20;$C$1 ;"") What I get is Oiveschilicumin without spaces in between, how to have the spaces in this formula Thanks in advance Jam |
Spaces
Because in your formula you are putting a space in place of the empty string
when the row 2 value is =20, but what the OP was asking for was a space between his $A$1,$B$1, and $C$1 results when the row 2 value is <20. As you say, if row 1 includes either leading or trailing spaces, then the change isn't needed, but text strings with leading or particularly trailing spaces often cause confusion elsewhere. -- David Biddulph "Teethless mama" wrote in message ... Niek Otten, Martin Fishlock, and David Biddulph all use "")&" "& (why???) you could use this instead " ")& To eliminate many spaces as the result . The header should have a space in front than OP could use the original formula. " wrote: Hi Anyone can help I have this problem in this formula A B C D 1 Olives Chili Cumin 2 12 19 15 =IF(A2<20;$A$1;"")&IF(B2<20;$B$1;"")&IF(C2<20;$C$1 ;"") What I get is Oiveschilicumin without spaces in between, how to have the spaces in this formula Thanks in advance Jam |
All times are GMT +1. The time now is 12:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com