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

[mercurial_maverick]

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

[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?

[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

[grazitti_sapna · Accepted solution]

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.

[mercurial_maverick]

Could you please paste the formulas here as a text? I'm unable to figure out the text in the first bracket () after area served in the second formula. @grazitti_sapna 

[grazitti_sapna]

Hi @mercurial_maverick,

Here you go:

trim(Regex_replace([Area Served,",",""))

Regex_replace([Area Served], "(\l+)(\u+)",'$1,$2',0)

[mercurial_maverick]

Perfect! That worked. Thank you much! @grazitti_sapna 

[tuhinarai]

Hey,

I am facing a similar issue, although the commas don't appear on the CSV file but rather when I open the output csv file in notepad. And I need the output in columns itself and not merge in rows. Is there a way to fix this?

Thanks