2

I need to remove a duplicate text in a cell but only if it is longer than 4 characters.

I have this formula that only works for any types of duplicate words:

=join(" ",unique(transpose(split(A1,", "))))

In this case, if a cell contains:

W3-X500 Samsung Galaxy W3-X500 5 inches and 5 different colors

It returns me:

W3-X500 Samsung Galaxy 5 inches and different colors

I will lose the second 5 character that instead I need.

How can I do it?

0

=REGEXREPLACE(ARRAYFORMULA(JOIN(" ", 
 UNIQUE(IF(LEN(TRANSPOSE(SPLIT(B1, ", "))), 
        IF(LEN(TRANSPOSE(SPLIT(B1, ", ")))>4, 
               TRANSPOSE(SPLIT(B1, ", ")), 
        IF(LEN(TRANSPOSE(SPLIT(B1, ", ")))<=4, 
               TRANSPOSE(SPLIT(B1, ", "))&
 "ᅇ"&CHAR(RANDBETWEEN(SIGN(ROW($A:$A))*1041, 1071))&
      CHAR(RANDBETWEEN(SIGN(ROW($A:$A))*1041, 1071))&
      CHAR(RANDBETWEEN(SIGN(ROW($A:$A))*1041, 1071))&
      CHAR(RANDBETWEEN(SIGN(ROW($A:$A))*1041, 1071))&
      CHAR(RANDBETWEEN(SIGN(ROW($A:$A))*1041, 1071))&"ᅇ", )), )))), 
 "\ᅇ([Б-Я]+)\ᅇ", "")

0

0

Complex problem for what might seem to be a simple thing, Max. The following formula will remove duplicates if they appear to be a model number (i.e., they begin with a capital letter and are not recognized as a mathematical number):

=ArrayFormula(TEXTJOIN(" ",TRUE,IF((VLOOKUP(transpose(split(A1," ")),{transpose(split(A1," ")),QUERY({ROW(A1:A100)},"Select * Limit "&COUNTA(transpose(split(A1," "))))},2,FALSE)=QUERY({ROW(A1:A100)},"Select * Limit "&COUNTA(transpose(split(A1," ")))))*(LEFT(transpose(split(A1," ")),1)=UPPER(LEFT(transpose(split(A1," ")),1)))+(ISNUMBER(transpose(split(A1," "))))>0,transpose(split(A1," ")),"")))

0

=ARRAYFORMULA(SUBSTITUTE(JOIN(" ", UNIQUE(TRANSPOSE(SPLIT(A1, ", ")&
 IF(LEN(SPLIT(A1, ", "))<=4, REPT("ᅇ", 
 TRANSPOSE(ROW(INDIRECT("1:"&COUNTA(SPLIT(A1, ", ")))))), "")))), "ᅇ", ""))

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.