habeebmustafa
3/23/2016 - 2:14 PM

From http://chandoo.org/wp/2014/01/13/combine-text-values-quick-tip/

Let say the cells you want to combine are in B2:B19.
In a blank cell, where you want to concatenate all the values type
=CONCATENATE(TRANSPOSE(B2:B19))
Don’t press enter yet.
Select the TRANSPOSE(B2:B19) portion and press F9. (related: debugging formulas using F9 key)
This replaces the TRANSPOSE(B2:B19) with its result
Now remove curly brackets { and }
Enter
Done!

If you cannot use F9 for any reason, use CTRL+=
If you want to add a delimiter (like space or comma) after each item in the text, you can use TRANSPOSE(B2:B19 & ” “) or  TRANSPOSE(B2:B19 & “,”)
If the range you want to concatenate is across columns (Say A1:K1), then you can skip the TRANSPOSE formula and write =CONCATENATE(A1:k1), Select A1:K1 and press F9, remove {}s.