Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Remove semi-colon when field is blank - CONCATENATE

Topic Labels: Formulas
1619 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Leah_Relish
6 - Interface Innovator
6 - Interface Innovator

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:
Screenshot 2022-11-12 121620

The current Formula I am using:
Screenshot 2022-11-12 121808

Much appreciated :slightly_smiling_face:

6 Replies 6

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
6 - Interface Innovator
6 - Interface Innovator

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

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
6 - Interface Innovator
6 - Interface Innovator

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.

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})
  ),
  "; $",
  ""
)

You are a genius! Thank you