# 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