Hi,
I have a contact details list where I have 3 columns with information on the person’s company all over the place. I need to merge them, but only if they contain text.
Example:
I’ve tried a few things with concatenating and also “right-click, merge cells”, but I can’t seem to find a working solution.
Any help appreciated. 
Thanks,
Patrick
It’s a shame you didn’t show the expected result in column D. Perhaps you would have had enough =TEXTJOIN(", ";1;A3:C3) if you are not upset with lines like “Google, Google, Google” or “Facebook, Facebook”
Hi,
Yeah. I realised that after.
I only want one of those, so “Facebook”. “Facebook, Facebook” is not acceptable.
I’ve edited my initial post now.
thank you for your help.
Simply don’t do it.
Generally merging is evil and will block (or at least seriously aggravate) any kind of evaluation that may one day be needed/wanted.
If there isn’t any evaluation intended: Why use a spreadsheet? Spreadsheets are dedicated to support evaluations.
If you need (for a presentation or a printout e.g.) a specific grouping, formatting to lines, other kinds of formatting:… do it in a different sheet based on formulas or on built-in tools and dedicated to the purpose.
BTW: Why does your example show Google twice, but Facebook amd Netflix only once in the result coulmn?
Hi,
Thank you for your insight here.
The issue here is that these are in our CRM system, so we’re doing a massive clean-up of 14,000 entries. There had been some bad imports, so people created new properties (columns here), so we ended up with duplications over time.
My dear colleague @Lupp is absolutely right - there is no easy way to do what you have in mind. The fact is that Calc does not yet have a UNIQUE() function. You will have to use the helper columns.
Enter the formula =TRIM(A2) in cell E2
Now in cell F2, enter a slightly more complex formula - =IF(TRIM(B2)=E2;"";TRIM(B2))
Cell G2 will contain an even more complex formula - =IF(COUNTIF(E2:F2;TRIM(C2));"";TRIM(C2))
Now go back to cell D2 and enter =TEXTJOIN(", ";1;E2:G2) (This is the same function that was mentioned in the comments, but it will work not with source data, but with the results of calculations in auxiliary columns.)
Stretch the cells with formulas to the end of the data, replace the formulas in column D with their values (copy and paste special or Data-Text to Columns), delete the auxiliary columns.
>Data>More Filters>Standard Filter>>Options offers the options No duplications and Copy result to.
The second mentioned option is displayed misleading: It does not copy a result, but only output it to the range beginning with the assigned cell.
The filtering itself may be “dummy”: No condition at all.
Hi @JohnSUN,
This works amazing!
Thank you.
Problem I have now is that I do have certain fields that have say “University of Southern California” and some UCSF. I end up with a joined field of “University of Southern California, UCSF”.
Now, I could take that cell and further trim off anything before the ", ", but some of my data contain a comma already, e.g. “MGH, Insitute of Brain Cancer”.
I’m working on a list of 14,000 entries, so while I can do some manual work for this clean up exercise it’s too many of those.
Any tips on how to solve this?
I tried to edit the textjoin formula to use a symbol that is not contained in my data, but it says “invalid value”. not sure why.
How about BEFORE the formation of the first of the formulas used the
Edit - Find & Replace(Ctrl+H) and simply replace all the University of Southern California with UCSF, or vice versa - all UCSF with the University of Southern California, repeat this for all the ambiguities encountered and only then use in the described way?
to use a symbol that is not contained in my data, but it says “invalid value”. not sure why
I’m too. First param can be ANY - "| ", "; ", " - " etc. Just try again
@Boxhamster: What symbol did you try to use as the list delimiter with TEXTJOIN()? I frequently use that function, and it accepted every string in that position (like :<>:), and also old control characters, even CHAR(0).
I tried "[ ".
Very strange… =TEXTJOIN("[ ";1;E2:G2) return Google[ Facebook[ Netflix for me
Hi,
I tried again in a new sheet and it works now. No idea what’s going on in the other one.
I’m nearly there now. I have joined the columns now like such “Texas Children’s Hospital!Johns Hopkins University”.
All I need now is a Regex that keeps the beginning and removes everything after the “!”.
Would this work? $.*!.
(In case you’re wondering, the first column is of higher quality and more recent, so it’s ok to remove the others)
Yes, it might work. But in such cases, I prefer another way. Select the entire column, choose Data - Text to Columns, Separator Options - Separated by - Other ! and mark all columns except the first one with Hide. For some reason it seems to me that it is faster, easier and safer.
A1 help! Thank you so much all! :))))