Concatenate ordered values in one single string

Question

I have a Google Spreadsheet where a person will add one name per row in a defined column (B). I need to generate a string like "<p>Adam - Becky - John - Robert</p>" in one single cell with alphabetically ordered names.

I can sort the names (in the second sheet) with the formula:

=sort(Sheet1!B:B, 1, TRUE)

But how can I concatenate these names in another cell (in the first sheet)?

Answer 1

Use the JOIN() function:

=join(" - ",sort(Sheet1!B:B, 1, TRUE))

SORT() returns an array of values. JOIN() concatenates an array with a joining string, in this case " - " without the quotes.

Answer 2

I found another solution thanks to this post in the Google Support forums.

=arrayformula(concatenate(Proper(unique(upper(query(B2:B31,"select B where B!='' order by B asc",0))))&" - "))

which also capitalizes each (unique) name.

After this I generate my <p> with this:

="<p>- "&D10&"</p>"

(I added the hyphen at the beginning since I'll have one hyphen in the end)

Answer 3

Thank you Karl, this almost solves my problem!

My last issue is that, since I don't know how many names I will have, I'm sorting the entire column B. The conseguence of this is that the joined cell will show my sorted names with a list of "-" after them.

Like this:

Adam - Becky - John - Robert - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - (and so on)

I thought I could add to the cell next to the names this formula:

=IF(B2="","empty", "with_content")

Can I add to the join a condition like:

where C2 = "with_content"

?