Remove null values after comma in a string and retain only non null values

Attached file shows a column output which has values concatenated and separated by comma. I want to retain only non-null values. For example the below values needs to be cleaned up to give Sales Foor, Tenant Space

,,,Sales Floor,,Tenant Space,,,

similarly there are rows that have commas only. which means null values are concatenated and separated by comma. These need to be cleaned up and replaced with blanks

prod_sims-2020-04-243709234410104708076.xlsx

Topic of Interest

Accepted answers

grazitti_sapna

Hi @mercurial_maverick,

Try the Formulas as given in the screenshot below and check if it works for you.

I hope I have been able to resolve the issue that you were facing.

All comments

Garrett

Parse values to rows, clean values, concatenate values back together into single row.

Balloons your data a bit, but no need for tricky String functions or RegEx.

Meet the need?

Remove null values.yxmd

grazitti_sapna

Hi @mercurial_maverick,

You can just use a simple formula tool with the expression given below:

trim(REGEX_Replace([Area Served],",", " "))

I hope this solves your problem

mercurial_maverick

HEllo @grazitti_sapna,

Thank you for your response. This did resolve most of the issue. However is there a way to omit only the unwanted commas and retain the ones between non null values. for example, "Sales Floor, Tenant Space".

Regards,

Ram

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335