Merge/Concatenate multiple columns with a separator characte

Discuss the spreadsheet application

Merge/Concatenate multiple columns with a separator characte

Postby AWoodShed » Thu Oct 10, 2019 4:43 am

Hi team,

I'm struggling to work out the correct formula to merge several columns together into a single cell, but the issue is that I want to put a "separator character" in between the cells, to break up the merged result.
But not all the columns contain data, in each row, so I'm ending up with "extra" separator characters in between the merged data, when a column is blank.

Example attached.
Sample Merge.ods
(14.75 KiB) Downloaded 8 times



I have incorrectly tried using a combination of =IF, AND and CONCATENATE functions, but cannot work out the correct blend of formula to achieve this.

ie: If A2 & B2 both contain data, then concat A2 & "my separator" & B2 = this part is easy.
But, if there's 3 columns of data to merge, I only want to put the separator between the columns that contained data
ie: if A2 & B2 contain data, put a separator in between, THEN, if C2 also contains data, put a separator in there as well, BUT, if B2 is empty, ONLY merge A2 & "my separator" & C2

Can anybody assist with the correct formulation I should use please?
NOTE: This is a small example - the number of raw columns won't always be 3 to merge, sometimes there may be more columns.
OpenOffice 4.1.5 running on various Windows Operating Systems (mainly 7 & 10)
AWoodShed
 
Posts: 21
Joined: Mon Aug 19, 2019 1:49 am

Re: Merge/Concatenate multiple columns with a separator char

Postby robleyd » Thu Oct 10, 2019 4:57 am

Are you trying to create the equivalent of a CSV file by concatenating the cells? What is your ultimate aim?
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2993
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Merge/Concatenate multiple columns with a separator char

Postby AWoodShed » Thu Oct 10, 2019 5:06 am

Ultimately yes, but this is a small data-set of columns within a much larger data-set spread out over several worksheets - the result is that 1 worksheet (which will contain this merged data) will eventually be converted into a csv.

But I need the merged result in a separate column, for use/referencing to other cells within other worksheets.

ie: I need to create a csv file (using my designated separator character) of the values within C3-F3, ignoring blanks, within Cell A3 (Column B is irrelevant and only for visual demonstration)

--------

I'm trying to avoid having to create helper-columns, to merge bundles of 2 columns together,

ie: H3 (for example) =if(and(A3<>"";B3<>"");concatenate(A3;" | ";B3);if(A3<>"";A3;if(B3<>"";B3;"")))

I3 = same as above, checking/merging C3 & D3

J3 = same as above, checking/merging H3 & I3

I can just see this recursive process being much too inefficient
Last edited by AWoodShed on Thu Oct 10, 2019 5:13 am, edited 1 time in total.
OpenOffice 4.1.5 running on various Windows Operating Systems (mainly 7 & 10)
AWoodShed
 
Posts: 21
Joined: Mon Aug 19, 2019 1:49 am

Re: Merge/Concatenate multiple columns with a separator char

Postby AWoodShed » Thu Oct 10, 2019 5:40 am

I worked out the attached solution, but this obviously only works on multiples of 2 columns at a time.


(New sample data attached to make it easier to understand)
Sample Merge 2.ods
(11.63 KiB) Downloaded 8 times



This obviously solves my initial problem, but is cumbersome, and considering my desired number of columns may not always be even-numbered multiples.

I'm hoping there is an easier way to achieve this?
Concatenate cells together, ignoring empty cells, add a separator character in between the results.

PS: Hoping to achieve this using a formula, not VB scripting or macros, as this won't be possible for me in this particular instance.
OpenOffice 4.1.5 running on various Windows Operating Systems (mainly 7 & 10)
AWoodShed
 
Posts: 21
Joined: Mon Aug 19, 2019 1:49 am

Re: Merge/Concatenate multiple columns with a separator char

Postby MrProgrammer » Thu Oct 10, 2019 8:25 am

AWoodShed wrote:I'm struggling to work out the correct formula to merge several columns together into a single cell, but the issue is that I want to put a "separator character" in between the cells, to break up the merged result. But not all the columns contain data, in each row, so I'm ending up with "extra" separator characters in between the merged data, when a column is blank.
We answered that very same question for you in August! Concatenate cells depending on contents. Perhaps this quote from that earlier topic will refresh your memory.
AWoodShed (on 2019-08-18) wrote:I am trying to combine the IF, AND and CONCATENATE functions together to create a combined text-string of 3 adjacent columns, but I only want to combine the cells that contain values.
I also want to put a separator symbol between joined cell values, when applicable.

I have locked your earlier topic. Several solutions are presented there.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3846
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Merge/Concatenate multiple columns with a separator char

Postby AWoodShed » Thu Oct 10, 2019 9:32 am

Sincere thanks MrProgrammer. I had completely forgotten I'd already asked this question several months ago, and I hadn't come back to see all the replies.

I will give those solutions a try.

Thanks all for your replies on the thread.

Just for my own info though - I've tried OpenOffice & LibreOffice several times over the years. One of them (I can't remember which one - I assume LibreOffice, since I've switched back to OO) would always crash, as I didn't have a Java virtual machine linked with it (I think that was the issue, but it's been ages since it happened)

Is L/O a much better program now, in terms of the query I had posted? I can't respond in the other thread as it's locked, but some of the replies indicate L/O has some more functions available that may be more use to me, as I use a lot of text strings in my spreadsheets. (eg =textjoin() option)
Does it still require having a JME installed?
OpenOffice 4.1.5 running on various Windows Operating Systems (mainly 7 & 10)
AWoodShed
 
Posts: 21
Joined: Mon Aug 19, 2019 1:49 am

Re: Merge/Concatenate multiple columns with a separator char

Postby Villeroy » Thu Oct 10, 2019 11:34 am

Neither one needs Java except for a handfull of special features. LibreOffice even less than OpenOffice.
If you want to generate csv, then save it as csv and adjust the separator by choosing "Text(csv) and "adjust filter settings" within the save-as dialog. Before saving you get another dialog where you can set | as column separator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: raynold3228 and 19 guests