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 agree to our terms of service, privacy policy and cookie policy

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