How to remove extra commas?

Question

excel

There are multiple columns

1         2        3        4
знач    знач2         знач4

I use
=CONCATENATE(RC[1];",";RC[2];",";RC[3];",";RC[4])

As a result, since the third column does not have a value, it turns out like

this , val2,,val4

How to remove these extra commas? if any of the columns have no value.

Answer 1

If EXCEL is version 2019 or higher, you can use the new TEXTJOIN function.
It allows you to specify a separator, ignore empty cells, and the actual range of cells.
https://easy-excel.com/how-to-join-text-from-sever...
Significantly simplifies concatenation.
Those. it all boils down to the formula =TEXTJOIN(",";TRUE;%a_here_range_of_cells to merge%). TRUE in the second argument just sets the empty cells to be ignored.

Answer 2

You can use the SUBSTITUTE function, or you can check for the presence of values ​​in the cells.