[Solved] Concatenate with separator, skipping empty cells

[SCFrolich]

I have a series of four cells that contain pertinent information that I wish to concatenate into a series of cells on another sheet.

The data is all numbers, but each of the series of four cells does not necessarily have an entry in each cell.

Example:

•     A     B     C     D
1     1    12     5     2
2     2     6           7
3           3     4    10

I wish to concatenate the cells A1:D1 (or A2:D2, etc.), but using a / as a separator. If there is no number in a cell, I do not want the separator to appear.

This is the formula that I am using (Please note that this is simply copied directly from my sheet and does not follow the example above precisely)
=CONCATENATE("";$Calculations.U33;IF(ISBLANK($Calculations.V33);" ";"/"&Calculations.V33);IF(ISBLANK($Calculations.W33);" ";"/"&$Calculations.W33);IF(ISBLANK($Calculations.X33);" ";"/"&$Calculations.X33))

It concatenates well enough, but I still get all of the separators.

Obviously something is wrong, but I can't for the life of me figure it out.

[MrProgrammer]

Hi, and welcome to the forum.

I wish to concatenate the cells A1:D1 (or A2:D2, etc.), but using a / as a separator. If there is no number in a cell, I do not want the separator to appear.

=SUBSTITUTE(TRIM(A1&" "&B1&" "&C1&" "&D1);" ";"/") or review Concatenate cells depending on contents.

If you need any further assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Specify exactly which cells on which sheet are to be concatenated.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[RusselB]

You might be better off using some hidden helper columns.
I would suggest just doing the concatenation of all of the cells, not worrying about if there is content or not in the first helper column.
If there is no content, then you'll simply get the separators back-to-back, so in the 2nd helper column, use the SUBSTITUTE function to search for the double occurrence of the separator and replace that with a single occurrence.
Actually, now that I have that written, I realize you only need the first helper column, as the 2nd is (or should be) what I think you are looking for as a final response.
Read up and test my suggestions, and, if you still have problems, as MrProgrammer stated, attach a copy of your spreadsheet.

[Lupp]

Actually needing an accumulating function for the creation of lists from the contents of cell ranges we are better off with LibreOffice which has the well working TEXTJOIN() in its versions V5.4 or higher.
No need of helpers, complicated tricks, or narrow limitations. The omission of empty list places e.g. is done by placing an 1 on the second parameter position instead of a 0.
In the given case

Code: Select all

=TEXTJOIN(";"; 1; A2:D2)

is all you need for the second row. And if you once should want to drop negative numbers, simply use

Code: Select all

{=TEXTJOIN(";"; 1; IF(A2:D2<0;"";A2:D2))}

entered for array-evaluation.

(I would dissuade from using the slash as a list separator in specific if numbers atre listed. Too much of ambiguity.)