Extracting unique text from a row of data

Why is this the result you expecting?

What are the criteria?

"Unique Text - This is the result I require

Parts, Bale Handling, Desvoys, Kverneland, Bale Spikes, Bale Spike"

Please try at A2
=TEXTJOIN(", ",,UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(">",,B2:K2),">",REPT(" ",900)),SEQUENCE(COLUMNS(B2:K2),,1,900),900))))

Thanks,

The text length limit is 32,767 characters

You may try this, you may expand range depend on the length of each cells

=TEXTJOIN(", ",,UNIQUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(">",,B2:K2),">",REPT(" ",LEN(TEXTJOIN(">",,B2:K2)))),SEQUENCE(COLUMNS(B2:K2),,1,LEN(TEXTJOIN(">",,B2:K2))),LEN(TEXTJOIN(">",,B2:K2))))))

or UDF

Please Login or Register  to view this content.

=UniqueT(B2:AG2,">")

Not only 1,373 characters. the formula need to substitute ">" to " " 1373 characters for each ">" so many times,.
total is over 32,767 characters.

You need to use UDF from #5

Hi,

I don't have access to functions such as UNIQUE and SEQUENCE, though this array formula** does not exceed the character limit for the file you provided:

=TEXTJOIN(", ",,IF(FREQUENCY(MATCH(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,SUBSTITUTE(C2:AG2," > ","</b><b>"))&"</b></a>","//b"),IF({1},FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,SUBSTITUTE(C2:AG2," > ","</b><b>"))&"</b></a>","//b")),0),ROW(INDIRECT("1:"&SUM(1+LEN(C2:AG2)-LEN(SUBSTITUTE(C2:AG2," > ","")))))),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,SUBSTITUTE(C2:AG2," > ","</b><b>"))&"</b></a>","//b"),""))

I'm sure Bo_Ry can adapt it using some combination of those newer functions to shorten it considerably.


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

XOR LX You are the true Legend.

Thanks a lot. I learn how to use FILTERXML to split text.


=TEXTJOIN(", ",,UNIQUE(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(">",,C2:AG2),">","</b><b>")&"</b></a>","//b")))

@Bo_Ry Good stuff! Now that's a nice-looking formula!

As well as not requiring the creation of large empty spaces, the other advantage FILTERXML/TEXTJOIN holds over the TRIM/MID/SUBSTITUTE/REPT set-up is that it can easily be applied to 2D ranges as well. A powerful, multi-dimensional string-parser!

Cheers