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

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
|

=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

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
|

Maybe...

=trim(a1&" "&b1&" "&c1)



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

--

Dave Peterson

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

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