How to Append Text to Each Cell in A Column in Google Sheets

Google Sheets has several functions in its arsenal to manipulate text which is useful when you need to append text to each cell in a column.

We can use the CONCAT and CONCATENATE functions for simple operations where we need to add text before or after specific text.

The rules for using CONCAT and CONCATENATE in Google Sheets are as follows:

• The CONCAT function only accepts two arguments, while the CONCATENATE function accepts two or more.
• Both functions allow us to join strings together. These functions return a single text value.

Let’s look at a quick example.

Let’s say I have a website that contains multiple pages. It’s updated a few times a week, and each new update has a specified page number.

The URL of the page follows the format:

https://<domain_name>/<page_id>

Given a list of page IDs, I would like to provide a column that contains the full URL, allowing anyone to click on it and view that specific page.

Using the CONCATENATE function, I can create several formulas which can help produce clickable links for our worksheet. 

Another use case would be to add certain prefixes or suffixes to certain columns. An example of this would be units of measurement and honorifics such as “Mr.” or “Ms.”.

Great! Let’s begin writing our own formulas so we can append text to each cell in a column in Google Sheets.

A Real Example of Using CONCATENATE Function to Append Text

Take a look at the example below to see how we can use the CONCATENATE functions to append units to a column of values.

We have here a list of values of various lot sizes. Unfortunately, the data comes from various countries, so the units are not all the same. Column C has the area with the correct unit added. 

Simply compute for Column C using the formula below:

 =CONCATENATE(A2," ",B2)

As you can see, we still need to explicitly add a space between the values A2 and B2 for a proper result.

The alternate way of writing the above formula is to use the CONCAT operator, written as an ampersand ‘&’.

=A2&" "&B2

You may create a copy of the spreadsheet above by clicking on the link below.

How to Use CONCATENATE Function in Google Sheets

1. Before we type our function, let’s populate our table with some values for us to join later.
   
   
2. We should then select a cell where we will input our CONCATENATE function. For this example, we will start with cell C2.
3. Next, we can input the equal sign ‘=‘, which must be included in every formula. We can then type the name of the function we want to use, which is  ‘CONCATENATE‘. 
4. The auto-suggest box may help you fill out the CONCATENATE  function even before typing out the entire function name. We may press the Tab button to auto-fill the Formula Bar with the full name of our function.
   A tooltip box may appear, which will remind you of what arguments are needed for this function. We can hide this box by clicking the upwards arrow in the top right corner.
   
   
5. We would need the cells containing the values we want to join into a single string for our arguments. In this particular example, we need the values in A2 and B2 to build our valid URL. You can see below that we’ve also added our own text as arguments to CONCATENATE to make sure the URL is in a valid format.
   
   
6. We can fill out the rest of the column with the CONCATENATE formula we typed earlier by using the fill-down method. Simply place the cursor at the bottom-right part of the original cell (which looks like a small blue square) and drag it down to fill an entire range.Now we have a column that has valid URL links!

Frequently Asked Questions (FAQ)

• How can I append text to a column and have it appear in a new line?
  
  Since we can’t simply type out the line break into our formula, we can use CHAR(10) which returns a line break.
  
  For example, =CONCATENATE(“Color:”, CHAR(10), “RED”) would return something like this:
  
  

• When appending numbers and dates, how do I specify what format is used?
  
  We can concatenate numbers and dates using various formats with the TEXT() function. In the example below, the user sees Column A in one particular date format. With the TEXT() function, we can convert the date into the date format we prefer.
  
  
  
  
  We use the following formula for achieving this:
  

  =CONCATENATE("Birthday: ",TEXT(A9 ,"dd/mm/yy"))
  

  The TEXT() function accepts two arguments: the date/number to be formatted and the format to use.

Now you know how to use the CONCATENATE function to append text to each cell in an entire column. You can combine what you learned here in this guide with various other Google sheets functions to create more powerful and effective spreadsheets.

Make sure to subscribe to our newsletter to be up-to-date on more useful Google Sheet guides just like this!

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Subscribe