Concatenate ordered values in one single string

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)?

• As an aside, the sorting formula could be simply =sort(Sheet1!B:B)
– user135384
Jan 18 '17 at 20:33

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.

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)

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"

?

• Please note, this isn't a discussion forum. The Answer space should only be used to offer a solution to the original question. You should instead edit your question and include this new information.
– ale
Jan 19 '17 at 14:35