How to Add Text to Excel Formula (Before, In, After)

When working with Excel formulas, have you ever wondered how to add text to them?

Excel formulas offer a lot of features that make writing formulas easier. But sometimes, you need to add some prefix at the start of the formula or some character at the end of it 🚩

The most obvious way of doing it is to type the text manually, but not the only way. In this step-by-step guide, we will learn some cool shortcuts and tips on how to add text to your formulas.

Download our sample workbook below to practice along with the guide now. Let’s get into it right away! 🥽

Table of Contents

Using CONCATENATE to add text in Excel formulas

The easiest way to add text to an Excel formula or any cell is to use the CONCATENATE function. It has different variations that you can use but all serve the same purpose.

You can use the ampersand symbol (&) to join two text strings in a cell easily as:

Or you could just use the CONCATENATE formula itself as:

In the newer versions of Excel, the spreadsheet software uses a more modern look of the CONCATENATE function as CONCAT 💻

You can use that to add texts to cells too as:

How to add text at the beginning of a formula

Adding text at the beginning of a cell is very simple, All you need to do is type the text string or reference the cell containing the string in the formula ➗

Say, for instance, I want to add “Sr No.” at the beginning of a cell in my data set.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula.

Step 3) Press Enter.

The ampersand operator will join both the text strings and return the result.

You can also use the CONCATENATE or CONCAT function here:

There is no difference between the two except for the syntax and comma, both formulas return the same result.

For quick application, you can also use cell references. That way you will have both the strings stored in different cells and you can simply use their cell reference to join them.

This way, you will able to change the text string to be joined easily without having to update it from each formula 😀

The formula for that would be

Cool, no? Copy the formula down to get the same results for the remaining cells 🧐

How to add text at the end of a formula

Adding text at the end of the formula is the same as in the case of adding at the beginning. Simply, change the position of the text string or cell reference to the end of the formula.

Let’s see how to do that below. In this example, we want to add the string “Offers” in each cell containing Monthly 📆

To do that,

Step 1) Select cell G2.

Step 2) Type in the following formula:

Step 3) Press Enter.

The formula will join the “Offers” string at the end of the cell.

You can do the same using CONCATENATE functions.

How cool is that? Try it now! 😃

How to add text at the beginning and end of a formula

You should be able to add text at the beginning and end simultaneously with your eyes closed now that we’ve seen both of those techniques independently.

All you need to do to combine text in a cell is to concatenate the string at the start and end of the formula.

Let’s understand this as we concatenate the “Double” and “Offers” string with Monthly as below.

Step 1) Select cell J2.

Step 2) Type in the following formula:

Step 3) Press Enter.

Repeat a similar process for the CONCATENATE function.

You’ve mastered the basics already 😀

How to join text in a formula combined from two cells

You can join text in a formula from two different cells using the same method as above.

Say, we want to join the two cells A2 and B2 with a separator.

To do that,

Step 1) Select cell C2.

Step 2) Type in the following formula:

Step 3) Press Enter.

The formula concatenates the two cells with a dash between them as in the image above.

You can do the same using the CONCATENATE Excel functions as:

And it’s done. How easy was that? 😀

How to add space in a formula

Adding space in a formula is similar to what we saw above. The only difference is that of the separator, we will change it from a dash to a space character.

Let’s see how to do that below.

Step 1) Select cell C2.

Step 2) Type in the following formula:

Step 3) Press Enter.

The function adds a space to the final result, the same happens for CONCATENATE functions.

As simple as it gets! 😉

How to use the CHAR function to add special characters in a formula

You can also insert special characters in your formula using the CHAR function. But for that, you need to first know the ASCII code of that character.

The ASCII code ranges from 1 to 255 so you can choose any printable character you like and supply its ASCII code. All you need to do is CONCATENATE the text string with the ASCII character output in a new cell.

Say, for example, we need to print the Euro symbol since it’s not available on typical keyboards. It’s code is 128 on the ASCII table 💻

To print the Euro symbol,

Step 1) Select cell F2.

Step 2) Type in the following formula:

Step 3) Press Enter.

And ta-da! The character appears with the text string in a cell as shown above.

You can achieve the same results using the CONCATENATE formulas:

You can try this with any other code you like from the ASCII table. Try it now! 😀

How to add text in the middle of a formula

We saw how to CONCATENATE space with something already stored in a cell in between the formula but how do you add a character in the middle or after a specific character count of, say, the first text string? 🧵

The key here is the LEN function. We use the LEN function to our advantage as it calculates the total length of the final string and then finds the location where we want to input our character.

Let’s see how this works below.

Say, we have the following sample data that contains the names of a famous mobile brand and their best-selling models. We want to add space before each model number but at the same position.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

Copy the formula down the remaining cells by double-clicking the Fill handle.

We can do the same thing using the CONCATENATE formula in our Excel spreadsheet.

You can do the same for any other text string once you know its length and character after which you want to add text.

How simple is that? 😃

How to add text before a certain character

Adding a character in the middle of the text string is easy if all the strings in your range are the same length. But what if they are of different lengths?

In that case, we use a more specific version of the above formula including the SEARCH function. It looks for that specific character in each cell and then inserts the piece of text before it 📄

Say, we have the following sample dataset, which contains some random text strings with one constant factor, they all have a dash in them. We will use this separator to insert the word.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

The formula would add the suggested string to all instances that come before the specific character. Copy the formula down the remaining cells to get:

You can use the CONCATENATE formulas to achieve the same result, simply add a CONCATEANTE at the beginning of the formula and remove the (&) ampersand sign instances from the formula as:

How to add text after a certain character

After all this practice, you should be able to do this with your eyes closed 😀

To add text after a specific character, simply remove the -1 and +1 arguments and the parameters will shift to bring the text string after your specified character.

To do that,

Step 1) Select cell B2.

Step 2) Type in the following formula:

Step 3) Press Enter.

And it’s done! The function places the LA string after the dash successfully 🥇

The logic of this function is that it first extracts text from the cell till the “-” character but doesn’t include it and then adds the text string LA enclosed in quotation marks.

Once done, it then extracts the text after the hyphen from the cell till the end of the string and concatenates both things together to give us the final result.

Copy the formula down the cells using the Fill Handle:

Or use the CONCATENATE formula as:

You can also use the TEXTJOIN function as an alternative to CONCATENATE – both serve the same purpose 💪

Conclusion

In this Microsoft Excel tutorial, we saw how to add space and text at the beginning, middle and end of our formula. We also saw how we can add special characters and place a text string before and after a specific text or character in our cells.

The CONCATENATE function is undoubtedly one of the most useful EXCEL functions to date. It allows you to join together any and all kinds of values, be it an alphanumeric and a numeric value or simple text values 2️⃣

To learn more about CONCATENATE and LEN functions, give the below articles a read.

How to Concatenate in Excel: CONCAT, Ampersand, and more

How to Use TEXTJOIN in Excel: Concatenate Cells (2024)

We hope you enjoy reading this article as much as we did creating it 🤗