Remove Spaces from CONCATENATE

[lesley burlingham]

I have a large amout of text in a =CONCATENATE(if function.

It is putting a , between each word. When there isn't an answer it is leaving a big space. Is it possble to remove this.

Code below.
=CONCATENATE(IF(F62="x",Request!B62&",","")&" "&IF(F63="x",Request!B63&",","")&" "&IF(F64="x",Request!B64&",",""))

 

[NARAYANK991]

Hi Lesley ,

Wrap a TRIM around your existing formula , as in :

=TRIM(
CONCATENATE(IF(F62="x",Request!B62&",","") & " " & IF(F63="x",Request!B63&",","") & " " & IF(F64="x",Request!B64&",","")))

Narayan

 

[NARAYANK991]

Hi Lesley ,

Even better , try this :

=SUBSTITUTE(TRIM(CONCATENATE(IF(F62="x",Request!B62&" ","") & " " & IF(F63="x",Request!B63&" ","") & " " & IF(F64="x",Request!B64,"")))," ",",")

Narayan

 

[jeffreyweir]

It looks to me like you're introducing those extra spaces needlessly in the first place, and that you don't actually need that CONCATENATE function in there, because those ampersands i.e. & are already doing all the concatenating:

=IF(F62="x",Request!B62, "")&IF(F63="x", ", " & Request!B63,"")&IF(F64="x",", " & Request!B64&",","")

Or you can use this:
=REPT(Request!B62, F62="x") & REPT(", " & Request!B63, F63="x") & REPT(", " & Request!B64, F64="x")