Calc: How to delete duplicate in cell

English
#1

I have a CSV file where in some cells there are duplicate URLs, there are two columns, 1st. the column has an image URL, and the second column may have an image URL or two that is a duplicate of what is in column 1, and these image URLs are repeated in the cell of 2nd column,
I don’t want to delete the row, just delete the duplicate values in the second column and let the value in column 1 remain
Here is a sample of it:
Column 1 row 1:
/content/7/663451/images/441289.jpg

column 2 row 1:
"/content/7/663451/images/441289.jpg , "/content/7/663451/images/441289.jpg , "/attachments/fe519c1e91c5e4983a70a2512fd5788b.jpg , "/content/7/663451/images/441289.jpg , "/content/7/663451/images/441289.jpg , "/attachments/311b904956e4fe56b59135c086603e4e.jpg

Column 1 row 2:
/content/1/1267889/images/496903.jpg

column 2 row 2:
"/content/1/558834/images/496903.jpg , "/attachments/dd6y9257ef3d95938e1fe0933697966h.jpg , "/content/1/558834/images/496903.jpg , "/attachments/w3461041b052c5d26220948f70f7fb55.jpg

using the Duplicate function does not work, appreciate any help, thanks
I use LibreOffice 7.4.2.3, windows 7 64bit

#2

Open the file in a text editor (notepad or something).
Copy two lines and post them directly as raw text data.

#3

thanks for your reply, here it is:

“”"/content/1/6634512/images/441289.jpg";"""/content/1/6634512/images/441289.jpg , “”/attachments/fe519c1e91c5e4983a70a2512fd5788b.jpg , “”/content/1/6634512/images/441289.jpg"
“”"/content/1/1168737/images/496903.jpg “;”""/content/1/1168737/images/496903.jpg , “”/attachments/dd6y9257ef3d95938e1fe0933697966.jpg , “”/content/1/1168737/images/496903.jpg , “”/attachments/w3461041b052c5d26220948f70f7fb55.jpg"

#5
  1. Delete all double-quotes and spaces from the entire file, before or after import. Quotes and spaces are not part of URLs anyway. Then you have 2 columns separated by semicolon where the second column has substrings separated by comma.
  2. =REGEX(SUBSTITUTE(B1;A1;"");"^,|,,|,$";"";"g") substitutes all occurrances of A1 in B1 with nothing and eliminates leading, trailing or duplicate commas resulting from the substitution.

P.S.
=REGEX(SUBSTITUTE(REGEX(B1;"""| ";"";"g");A1;"");"^,|,,|,$";"";"g") does it all in one step

#4

Where can I find that function?

For a probable solution see attached example:
disask94366DeleteSpecifiedListElementsFromCelltexts.ods (33.9 KB)
The data from the .csv must be correctly imported into a spreadsheet document.

BTW: The comma as a list separator should be avoided. As soon as the elements can be ordinary numbers it may conflct with the decimal separator. Most locales use the comma for the purpose.

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