Remove semi-colon when field is blank - CONCATENATE

Leah_Relish · ‎Nov 12, 2022

I would like to omit the semi-colon when a field is blank, and I know it can be done using IF, however my brain cannot wrap around how to pull it together! Can anyone help?

Here’s my example:

The current Formula I am using:

Much appreciated :slightly_smiling_face:

kuovonne · ‎Nov 12, 2022

There are several possible formulas. Here is one that assumes that you will always havve a {Manufacturer}

CONCATENATE(
  {Manufacturer},
  IF({Product}, "; " & {Product}),
  IF({Operating System}, "; " & {Operating System}),
  IF({Material}, "; " & {Material}),
  IF({Colour}, "; " & {Colour}),
  IF({Other Details}, "; " & {Other Details})
)

Leah_Relish · ‎Nov 12, 2022

Thank you! I won’t always have a manufacturer, but I tried your formula and I’m not sure what I am doing wrong:

kuovonne · ‎Nov 12, 2022

Is the problem that the formula won’t save, or is the problem that the formula gives the wrong result?

Is there a reason why the line breaks in the formula were removed?

Leah_Relish · ‎Nov 12, 2022

So, I got it to work after retyping it several times! Must have been doing something weird. But I am not able to omit the first ; if Manufacturer is empty.

kuovonne · ‎Nov 12, 2022

REGEX_REPLACE(
  CONCATENATE(
    IF({Manufacturer}, {Manufacturer} & "; "),
    IF({Product}, {Product} & "; "),
    IF({Operating System}, {Operating System} & "; "),
    IF({Material}, {Material} & "; "),
    IF({Colour}, {Colour} & "; "),
    IF({Other Details}, {Other Details})
  ),
  "; $",
  ""
)

Leah_Relish · ‎Nov 14, 2022

You are a genius! Thank you