[Solved] Concatenate with separator, skipping empty cells

Discuss the spreadsheet application

[Solved] Concatenate with separator, skipping empty cells

Postby SCFrolich » Fri Jan 03, 2020 12:28 am

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.
Last edited by MrProgrammer on Sun Jan 12, 2020 5:31 am, edited 4 times in total.
Reason: Added [pre][/pre] tags to example; Changed title, was: Concatenation issues; Tagged ✓ [Solved]
OpenOffice 4.1.6 on Windows 10
SCFrolich
 
Posts: 6
Joined: Fri Jan 03, 2020 12:13 am

Re: Concatenation issues

Postby MrProgrammer » Fri Jan 03, 2020 1:55 am

Hi, and welcome to the forum.

SCFrolich wrote: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
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3962
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Concatenation: Use separator, skipping empty cells

Postby RusselB » Fri Jan 03, 2020 2:28 am

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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6157
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Concatenation: Use separator, skipping empty cells

Postby Lupp » Fri Jan 03, 2020 2:58 am

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   Expand viewCollapse view
=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   Expand viewCollapse view
{=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.)
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2929
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests