Add a character, word, other text to multiple cells in Excel

Sometimes, when working with large datasets in Excel, you need to add a specific piece of text to every cell in a column — either at the beginning or the end of each value.

Add text at the start or end of cells with formulas

You can solve this with the CONCATENATE function or simple concatenation using the ampersand (&) operator. However, it’s a multi-step process:

• Insert a helper column next to your original data
• Enter a formula in the first row using CONCATENATE or &, for example:

=CONCATENATE(A1," word")

or

=A1&" word"

• Drag the formula down the helper column to cover the full range
• Replace formulas with values
• Copy the results back over the original column
• Delete the helper column

Here’s an example adding a question mark to the end of each value (you can replace it with any text inside the quotes):

To add quotes around a value, you’ll need escaped double quotes, which in Excel means four in a row:

=""""&A1&""""

This places the value from A1 inside quotation marks.

Automating the process with !SEMTools

If you perform this task often, manually setting up helper columns gets tedious. That’s why this process is automated in the !SEMTools add-in for Excel. Below are a few examples of adding text to every cell with it.

Add a character to the end of each cell

Select your range, run the macro, enter the desired character, and click OK. The changes are applied directly in the same column.

Add characters to both the start and end of each cell

A real-life example: an executive assistant is preparing the final materials for a high-profile partner conference. One of their key tasks is to format the list of attending companies for name badge printing.

This is crucial to ensure a uniform and professional appearance for all name badges.

The initial list, received in a spreadsheet, contains the company names in a simple column. The raw data includes entries such as:

• Aurora Imports
• Cedar & Stone
• Eclipse Group
• etc.

Following the event’s strict branding guidelines, the assistant must reformat each entry. The requirement is to present the company name in a formal manner on the badges. The specific format is “Delegate from: ‘Company Name'”, where the company name is enclosed in quotation marks.

Firstly, we need to add quotes both before and after company names:

Add characters to the start of each cell

To achieve this, the assistant creates a new column in the spreadsheet. For each company, they add the phrase “Delegate from: ” before the name and enclose the name itself in quotation marks:

Add characters to each word in cells

The methods above modify the cell’s value regardless of its content — even if it’s empty. They are considered as changing cells.

When you need to add a character before or after each word in a cell, that’s considered changing words, because the number of words per cell can vary, or there may be none at all (in that case nothing will be added). For that scenario, see my separate article with formulas and functions for adding characters before or after each word in cells.

This post is also available in RU.