Add Text to The Beginning or End of All Cells

Adding the same texts to the beginning of all cells in excel must really be stressful and exhausting especially if you have thousands of data in your Microsoft Excel spreadsheet.

Imagine a scenario where you have to add a specific text to the beginning or end of all the cells in your spreadsheet. This will be very cumbersome if you have to do it manually (depending on how much data you are dealing with).

Excel provides us the ability to add text to the beginning, or the end of all cells or a selected number of cells. There are quite a number of ways to achieve that.

In this article, simpler methods that will aid you to do this fast and easily will be explained. There are four methods that will be discussed in this article aided with step-by-step guides and graphical representations to make it easier for you.

Method 1: Using the ampersand (&) operator/ formula

The ampersand (&) is a symbol that is used to join several text strings in excel into one. With the help of the ampersand symbol, you can add text to the beginning or end of all cells in your spreadsheet.

Let’s say you have to add “Mrs” to a list of names in an excel spreadsheet. 

The steps below will aid you to do this in a very simple manner.

  1. Click on the first cell of the column where you want the converted names to appear (B2).
  2. Type equal sign (=), followed by the text “Mrs. “, followed by an ampersand (&).
  3. Select the cell containing the first name (A2) and press enter.
  4. You will notice that the title “Mrs.” is added before the first name in the list.
  1. You can drag the fill handle to apply the effects to the rest of the names or copy the highlighted formula to the rest of the cells in the column by simply double-clicking the fill handle at the bottom right of cell B2.

This enables you to insert the text “Mrs.” at the beginning of all the cells with the names. You can also use the same process (but changing the position of “&” and your text) to add any text as a prefix to any data in an Excel cell.

In effect, what happens is seen in the picture below.

Using the ampersand (&) operator to add texts to the end of cells.

Now let’s see how to add texts to the end of names in the previous list above. Let’s assume we want to add “AC” at the end of the names. Follow the steps below:

  1. Click on the first cell of the column where you want the converted names to appear (C2 in our case).
  2. Type equal sign (=)
  3. Select the cell containing the first name (B2 in our case).
  4. Next, insert an ampersand (&), followed by the text “(AC)” press the enter key.
  5. You will notice that the text “(AC).” is added after the first name in the list.
  1. Now you can either copy the formula to the rest of the cells by double-clicking on the fill handler highlighted or alternatively just dragging it down the list to get the same results as seen below.

Method 2: Using the Flash Fill Feature

Flash fill is a new feature that analyzes the pattern of what you’re attempting to do and then replicates it across all cells in a column. This feature is only available for excel versions from 2013 onwards.

With the help of flash fill, you can also add text at the beginning or end of all cells or a selected number of cells in Microsoft Excel. Let’s first take a look at how we can use flash fill to add text to the beginning, then we will also look at using it to insert text to the end of the cell.

Using the Flash fill feature to add text to the beginning of all cells

  • Click on the first cell of the column where you want the converted names to appear (B2).
  • Manually type in the text Mrs., followed by the first name of your list and press the enter key.
  • Click on cell B2 again.
  • Under the Data tab, click on the Flash Fill button (in the ‘Data Tools’ group). Alternatively, you can just press CTRL+E on your keyboard (Command+E if you’re on a Mac).

As the name suggests, when the flash fill button is clicked it fills the columns with the pattern you are working within a flash!

Using the flash fill to add text to the end of all cells in a column

  • If you want to add “AC” to the end of the names, follow the steps below: Click on the first cell of the column where you want the converted names to appear (C2).
  • Manually type in or copy the text from column B2 into C2.
  • Add the text “(AC)” after that.
  • Under the Data tab, click on the Flash Fill or press CTRL+E on your keyboard (Command+E if you’re on a Mac).

Every cell then gets filled with the pattern as seen above. Simple and fast.

Method 3: Using the concatenate function

The CONCATENATE function works just like how the Ampersand symbol works. It enables you to join any number of texts. You can therefore use the CONCATENATE function to add text at the beginning or end of all cells or a selected number of cells.

The CONCATENATE function has the following general syntax: 

=CONCATENATE(text1, [text2], …), where text, text 2,..etc are the substrings that you want to combine together

The following steps will help you to apply the concatenate function to a dataset acquired.

  • Click on the first cell of the column where you want the converted names to appear (B2).
  • Type equal sign (=).
  • Enter the function CONCATENATE, followed by an opening bracket (and type the title “Mrs.” in double-quotes, followed by a comma (,).
  • Select the cell containing the first name (A2)
  • Place a closing bracket. In our example, your formula should now be =CONCATENATE (“Mrs. “, A2) and press enter.
  • You will notice that the title “Mrs.” is added before the first name on the list.

Duplicate this formula over the rest of the column’s cells. To use the fill handle, simply double-click it (located at the bottom right of cell B2). Alternatively, you can get the same effect by dragging down the fill handle.

That’s it; the title “Mrs.” should now appear before each name in all of the cells in column B.

Using concatenate to add text to the end of all cells

  • Click on the first cell of the column where you want the converted names to appear (C2 in our example) and Type equal sign (=).
  • Enter the function CONCATENATE, followed by an opening bracket (and Select the cell containing the first name (B2 in our example).
  • Next, insert a comma, followed by the text “(AC)”.
  • Place a closing bracket. In our example, your formula should now be: =CONCATENATE(B2,” (AC)”) and Press the enter Key.
  • You will notice that the text “(AC).” is added after the first name in the list.
  • copy this formula to the rest of the cells in the column. Simply double-click the fill handle (located at the bottom right of cell C2).

Due to the use of a formula, whatever changes applied to column A reflects in column C.

Just so you don’t get an error alert when you finally have your column C and want to do away with columns A and B, convert the formula results to permanent values copying them and pasting them as values in the same column. 

  • Right-click the C column and select paste options.
  • From the pop-up, select the values menu.
  • Now you can go ahead and delete columns A and B if you need to.

Add characters to the beginning and end of a string

It is worth noting that, the CONCATENATE function as well as any of the previously discussed methods can be used to add text to the beginning and the end of all cells in an Excel spreadsheet at the same time.

To add characters or texts to the beginning and end of a string all at once, you can use this formula

=CONCATENATE(“Mrs.”, A2, ” (AC)”)

This would give you a straight result as displayed below:

techpady virtual classroom

Digital School Academy

Join our telegram channel and benefit from our regular digital skills trainings and also become a tech guru with very simplified tech tutorials

Method 4: Using VBA code to Add Text to the Beginning of all Cells in a Column

To use VBA to achieve this result, you will need to add the developer menu to your menu ribbon. By default, the developer menu isn’t part of the menu ribbon in Microsoft Excel. To add the developer menu, follow the steps below.

How to enable the developer tab in Excel

To enable developer tab in Microsoft Excel:

  • Click on File and click on Options (which is usually the last menu item on the left pane)
  • On the dialog box that pops up, click on customize ribbon from the left pane menu
  • Check the Developer option which will be unchecked by default as shown in the image below.
  • Click the OK button after checking the Developer checkbox
  • The dialog box will automatically close and you will find that the developers tab has been added to your menu ribbon.

Now that you have the developer tab available, let’s see how you can use a VBA code to add text to the beginning of a cell in Microsoft Excel.

The sample code below can be used to add the title “Mrs” to the beginning of the cells.

Sub add_text_to_beginning()
Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = "Mrs. " & cell.Value
Next 
End Sub
  • Locate the developer tab on your menu ribbon and click on visual basic

On clicking on Visual Basic, the VBA window will be opened. A keyboard shortcut that can help you save time when opening the VBA window is (ALT + F11)

  • Once your VBA window opens, Click on the Insert tab and on the dropdown that shows up, click on Module. Now you can start coding. Type or copy-paste the sample lines of code provided above into the module window. Your code is now ready to run.
  • After inputting your code, you can move back to your excel sheet and select the range of cells containing the text you want to convert. Make sure the column next to it is blank because this is where the code will display the results.
  • To apply the script, click on the developer tab and click on Macros, choose the specific macros (“add_text_to_beginning”) and click the run button.

Using VBA to add text to the end of all cells in a column

The VBA code below will enable you to add some text to the end of all cells in a given column within an Excel Spreadsheet.

Sub add_text_to_end()
Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = cell.Value & " (MD)"
Next cell
End Sub
Spread the love

Similar Posts

Leave a Reply

Your email address will not be published.