Merge/Concatenate multiple columns with a separator characte

[AWoodShed]

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 109 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.

[robleyd]

Are you trying to create the equivalent of a CSV file by concatenating the cells? What is your ultimate aim?

[AWoodShed]

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

[AWoodShed]

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 129 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.

[MrProgrammer]

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.

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.

[AWoodShed]

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?

[Villeroy]

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.