How to add text to the beginning or end of all cells in Excel

You can watch a video tutorial here.

Excel has several functions to help manage text. Using these functions you can clean text, separate it or merge it. These operations are performed frequently when cleaning data particularly when it has been imported from another application. When cleaning data to make it more presentable, or to use it in another application, you may want to add some text to words in a column. In this example, we will add text to a column of names that is going to be used in form letters that are being sent out. There are three ways of doing this: 

1. CONCAT() function: this joins or concatenates the text and the formula
   1. Syntax: CONCAT(text1, text2, …..textN)
      1. text1, text2: the pieces of text to be joined
2. Ampersand (&) operator: this joins or concatenates the text and the formula
3. Flash fill tool: this recognizes a pattern in the first cell and fills the rest of the column in a similar manner

Option 1 – Use the CONCAT() function

Step 1 – Create the formula

• Select the cell in the column next to the name
• Enter the formula using cell references:

= CONCAT(“Sales Executive ”,Name,” is promoted to Senior Sales Executive”)

• Press Enter

Note: Make sure that a space is inserted at the end of the first piece of text and the start of the last piece of text. This is so that there are spaces before and after the name and the words are not joined together.

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Option 2 – Use the Ampersand (&) operator

Step 1 – Create the formula

• Select the cell in the column next to the name
• Enter the formula using cell references:

= “Sales Executive ”& Name &” is promoted to Senior Sales Executive”)

• Press Enter

Note: Make sure that a space is inserted at the end of the first piece of text and the start of the last piece of text. This is so that there are spaces before and after the name and the words are not joined together.

Step 2 – Copy the formula

• Using the fill handle from the first cell, drag the formula to the remaining cells

OR

1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Option 3 – Use the Flash fill feature

Step 1 – Create the pattern

• Select the cell in the column next to the name
• Type the first name with the text that you want to be added to it:

= “Sales Executive <Name> is promoted to Senior Sales Executive”

• Press Enter

Step 2 – Use Flash Fill

• Go to Home > Editing > Fill
• Select Flash Fill

Step 3 – Check the result

• The pattern is copied to the rest of the cells in the column