Remove duplicate strings within a cell

English
#1

Hi I have a list of names copied from a webpage that when pasted in Libre Calc they appear twice within one cell.
e.g.
Example of what I need. Eliminate repeated strings.ods (10.2 KB)

I have in column B
name1 name1
name2 name2
name3 name3

How to remove all repeated names, leaving the first intact?

The result I expect is
name1
name2
name3

Note: I’m a pretty basic user of |Libre Calc and I don’t know much about programming and stuff but I can follow steps.
Note: I saw there a is a code someone made for excel for that matter but I don’t know how to transfer those steps into Libre Calc or if there is a more simpler solution.

Thank you very much in advance.

#3

If the names are only a single word then the easiest is to ensure C column is blank, if needed insert a column. Select the names, then click Data > Text to columns and make sure space is ticked under Separator options, you should see two columns with a name in each in the preview. Click OK.

If there are double-barrelled names, e.g. "Mary Jane Mary Jane " then you need something different. I don’t know if there would be a trailing space or not, but this formula in column C should extract the names, =LEFT(B3,INT(LEN(TRIM(B3))/2)) even with a trailing space. After acquiring the names copy column C and Edit > Paste Special > Paste Unformatted Text to paste the names over the formula. You can then delete column B

1 Like
#5

I tried to apply your formula but since I have the spanish verison of Libre Calc I had to search the conversion for the formula in this link:
“Formulas de Calc Español - Inglés - Apache OpenOffice Wiki”
but the conversion for TRIM wich in Spanish should be REDUCIR seems to not be any formula in my program, wich is version 7.5.2.2.

I guess that’s a question for another topic, but thank you very much I think it should have worked perfectly if I had the English version.

#6

TRIM removes spaces at the beginning and end of a string in a cell, so in Spanish ESPACIOS (from TRIM • Excel-Translator)
TRIM can be left out, it is belt and braces, so just =LEFT(B3,INT(LEN(B3)/2)) but if there is a trailing space in B then the trailing space will still be there in C which might cause problems if you use VLOOKUP or other functions.

NOTE: you can set Calc to use English function names, the setting is in Tools > Options > LibreOffice Calc > Formula, tick box Use English formula names or similar

1 Like
#7

Great!! Works perfect too thanks you for the extra explanation, I used the excel translator you mentioned, applied your first formula and everything works as expected, learned a lot, in the future I will use the settings to use the English formulas to not complicate myself :+1: :+1: :+1:

#2

Put in a new column on row 3:
=LEFT(B3,(LEN(B3)-1)/2)
and drag the formula down.
Once done, you can copy the result of the new column back to the first column using the paste special option “Values only”.

2 Likes
#4

Thank you so much it works like magic!! :smiley:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.