How to combine a cell value and a text-string

Sometimes you may want to write a formula which combines the value(s) from some cells with some other text, and puts the result into the one cell.

For example, I have a spreadsheet which generates the HTML code statements for drawing a table, based on the values which I put into a table-area on the spreadsheet. And I need to combine the values from the table area with other HTML commands like "" and ""

The way to do this in Google Spreadsheets is to use a string concatenation operator (&) or a string-concatenation function ( CONCAT( , ) or CONCATENATE(...) )

For example, I label the value from C5 with the text "Score type:", and so in the results column which I use to make the table-cell code for it, the formula is:

=CONCAT("<td>Score type: ", CONCAT(C5, "</td>")) 

Or a less verbose way to achieve the same thing is: 

 ="<td>Score type: " & C5 & "</td>"

What's the difference between "&" CONCAT and CONCATENATE?

The CONCAT() function and the "&" operator do the same thing, so whether you use one or the other is is about which one looks nicer to you.

The CONCATENATE() can be used to join any number of text strings together, so another way to write the statement is

=CONCAT("<td>Score type: ", C5, "</td>"))

In general, it's best to use the option which makes the formula the most readable, or easiest to diagnose if something goes wrong.

So often I will use the CONCAT() function in each one of a set of helper columns, and then use one CONCATENATE() function at the end to join them all together, because this is easier to debug than trying to do a lot of string operations all in the one cell.

For example:

Was this helpful? You might also like:

• Referring to  a range of cells in another worksheet
• How to combine values from a range of cells

How to combine a range of values into one cell, with a character in between them

If you have a list of items in a spreadsheet, set up like a table, then you can use the join function to combine the values and display them all together in a horizontal list, with some characters (eg a comma and a space) in between tehm..

For example, in my summer-school management spreadsheet I have a list of teachers in column C, currently in rows 1 (heading) to five.

 Teachers
-----------
 Fiona
 Patrick
 Sean
 Lynne


To put the teacher-names into the one cell, nicely formatted with a comma and a space between each one, I could use the JOIN() function, like this:

= JOIN( ", " ; C2:C5 )

But there are two problems:

• The result is "Fiona, Patrick, Sean, Lynne, " - there is an extra "comma space" at the end

• If the number of teachers changes, the I have to adjust the formula

A better option it to also use the FILTER() function, to remove values that you dont't want in this case, blanks which are represented by "".

So the formula becomes:

 = JOIN( ", " ; FILTER(C2:C9999; NOT(C2:C999 = "") )) 

This says to join all the values in cells C2 through to C9999 together, to put a comma and space between each one, but to leave out any that are blank,

Using this, my list becomes "Fiona, Patrick, Sean, Lynne", and I can add up to 9998 names in the Teachers column and it still works.

Extra for Experts

If I had used named ranges, and so didn't have a heading at the top of the column, then the formula could become even more flexible, with no limit to the number of rows included, like this:

= JOIN( ", " ; FILTER(C:C; NOT(C:C = "") ))
or

= JOIN( ", " ; FILTER(Teachers; NOT(Teachers = "") ))