CODEWITHSUNDEEP
excellistexcel-formulaconcatenationunique
Tyler B
Tyler B

Reputation: 29

Excel: How to concatenate a list produced by unique() with additional text following, in one cell

I have the following formula:

=CONCATENATE(TRANSPOSE(UNIQUE(FILTER(H2:H1048576, H2:H1048576<>"")))&"_","Feedback",TEXT(TODAY()," mm.dd.yy"))

It is producing the following: enter image description here

I want the call value to be "AAA_BBB_CCC_DDD_Feedback 01.08.21". How do I do this?

Upvotes: 0

Views: 551

Answers (1)

BigBen
BigBen

Reputation: 50008

Use TEXTJOIN instead of CONCATENATE. Also you don't need FILTER (you can ignore blanks with TEXTJOIN), nor TRANSPOSE.

=TEXTJOIN("_",TRUE,UNIQUE(H2:H1048576))&"_Feedback"&TEXT(TODAY()," mm.dd.yy")

enter image description here

Slightly shorter option (credit to @JvdV):

=TEXTJOIN("_",,UNIQUE(H2:H1048576),TEXT(TODAY(),"F\e\e\d\back mm.dd.yy"))

Upvotes: 2

Related Questions