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