Joining multiple fields, removing extra spaces and adding a comma.

Support
1

Hi,
I have multiple fields I need to concatenate and separate with a comma, obviously I do not want the empty fields to have a comma so I am trying to combine TRIM and SUBSTITUTE. The TRIM is working, but the SUBSTITUTE is not. I must be formatting the formula incorrectly, can someone please point me in the right direction?
SUBSTITUTE(TRIM(A53+" “+C56+” “+C57+” “+C58+” “+C59+” “+C60+” “+C61+” “+C62+” “+C63+” “+C64+” “+G56+” “+G57+” “+G58+” “+G59+” “+G60+” “+G61+” “+G62+” “+G63+” “+E64+” “+A66+” “+C66+” “+E66,” “,”, "))

Thank you for your time
Robyn

2

Hi,

SUBSTITUTE(text,old_text,new_text,[instance_num])
Old_text you would like to find should be " “, new_text is " ,”, these two are SUBSTITUE() variable, should be after the first right parenthesis.

You could try this out:
SUBSTITUTE(TRIM(A53+" “+C56+” “+C57+” “+C58+” “+C59+” “+C60+” “+C61+” “+C62+” “+C63+” “+C64+” “+G56+” “+G57+” “+G58+” “+G59+” “+G60+” “+G61+” “+G62+” “+G63+” “+E64+” “+A66+” “+C66+” “+E66),” “,”, ")

1 Like