Address lines

[nad_]

Hi. In my database the user will enter their address. Some address will have up to 7 lines and some of it is optional. Since some is optional when I concatenate the text in virtual column, the will be spaces in lines that is blank. Can anyone help me with this problem? I want to make the next line 'automatically' filled the blank line so there will be no awkward spaces in between the lines.

Btw currently i used this formula to display the address in virtual column:

CONCATENATE(
[CH_Add1],"
",[CH_Add2],"
",[CH_Add3],"
",[CH_Postcode]," ",[CH_City],"
",[CH_State],"
",[CH_Country])

[TeeSee1]

Use

IF(ISNOTBLANK([COL]), ([COL] & ","), "") in place of just [col].

Adjust "," depending of the position of the column.

[Steve · Accepted solution]

Try:

SUBSTITUTE(
  CONCATENATE(
    LIST(
      [CH_Add1],
      [CH_Add2],
      [CH_Add3],
      ([CH_Postcode] & " " & [CH_City]),
      [CH_State],
      [CH_Country]
    )
    - LIST("")
  ),
  " , ",
"
"
)

[nad_]

Thank you so much for helping. 😊

[dbaum]

Concatenate values with a delimiter: How to simula... - Google Cloud Community