How to Add Characters in Excel (with Formula, Flash Fill & VBA)

You may need to add characters in Excel to the beginning, end, or in any position of all cells in a selection at times. I guess everyone knows how to do this manually, It must take a long time to manually enter the text into each cell. In this article, we are going to show a number of easy ways for adding the same characters to a selection.

Below a method where characters are added using the flash fill feature is presented. This method is super convenient and time saving.

Download practice Workbook

Download this practice workbook to exercise while you are reading this article.

5 Easy Ways to Add Characters in Excel

Consider the following scenario: you have data in existing cells that contain names. You might want to put a prefix at the beginning of each cell, a suffix at the end, or some text in front of a formula.

Method 1: Ampersand Operator (&) to add characters in Excel

The Ampersand (&) is an operator mainly used for joining multiple text characters into one.

we will use it to put characters at the before/after of all the cells in a range.

Steps:

• At first, click on the first cell of the column where you want the converted names to appear (C5).
• Type equal sign (=), followed by the text “Professor ”, followed by an ampersand (&).

• Select the cell containing the first name (B5).
• Then press Enter to see the result.
• Finally drag to AutoFill the cells.

• Then drag the fill handle to cell C12 to see the range of cell C5:C12 is now filled with names with profesor appended in front.

Method 2: CONCATENATE Function to add characters in Excel

The CONCATENATE function is an Excel function that allows you to insert text at the start and end of a text string.

The CONCATENATE() function is similar to the ampersand (&) operator in terms of functionality. The only difference between the two is how we use them. We can apply this function at the beginning of the end of the text. In this section, we will discuss both of them.

2.1 CONCATENATE to Add Characters to the Beginning of all Cells

Now let us see how to add some characters to the beginning of every name in the dataset. Let us say that you want to add the text “Professor ” at the end of every name. Follow these steps to learn this method.

Steps:

• At first, click on the first cell of the column where you want the converted names to appear (F5).
• Type equal sign (=) to type formula.
• Enter the function CONCATENATE.
• Type the title “Professor ” in double-quotes, followed by a comma (,).
• Select the cell containing the first name (E5)
• Place a closing bracket. In our example, your formula should now be

• Press Enter.
• Drag down the fill handle to achieve the same effect.

• Finally you will notice that the title “Professor ” is added before the first names on the list.

2.2 CONCATENATE to Add Characters to the End of all Cells

Now let us see how to add some characters to the end of every name in the dataset. Let us say you want to add the text “(USA)” at the end of every name.

Steps:

• At first, click on the first cell of the column where you want the converted names to appear (C5 in our example).
• After that, Type equal sign (=) to type formula.
• Enter the function CONCATENATE.
• Then select the cell containing the first name (B5 in our example).
• Next, insert a comma, followed by the text “(USA)”.
• Place a closing bracket. In our example, your formula should now be:

Formula Text

• Press the Enter.
• Drag down the fill handle to achieve the same effect

• Finally you will notice that the text “(USA).” is added after the first names in the list.

Method 3: Flash Fill to Add Characters in Excel

Excel’s flash fill feature works magically. If you’re using Excel 2013 or later, you’ll be able to use it.

Excel’s pattern recognition skills are used in this function. It recognizes a pattern in your data and fills in the rest of the column’s cells with the same pattern for you.

3.1 Flash Fill to Add Text to the Beginning of all Cells

Steps:

• At first, click on the first cell of the column where you want the converted names to appear (F5).
• Manually type in the text “Professor ”, followed by the first name of your list
• Press Enter.

• You will notice there is plus sign in the corner of the C5 cell.
• Drag this fill handle while pressing right mouse buttton

• Then release the mouse button, and from the right context menu, click on the Flash Fill.

• Finally this will copy the same pattern to the rest of the cells in the column… in a flash!

3.2 Flash Fill to Add Text to the End of all Cells

Steps:

• At first, click on the first cell of the column where you want the converted names to appear (C5).
• Manually type in the text “(USA)”, followed by the first name of your list
• Press Enter.
• Click on cell C5 again.
• Under the Data tab, click on the Flash Fill button.

• Finally this will copy the same pattern to the rest of the cells in the column… in a flash!

Method 4: Add Characters in Excel before/after Specific Nth Character

To add a specific text or character at a specific location in a cell, break the original string into two halves and add the text between them.

Steps:

• If you want to add a (-) after the 5th character between the words James and (USA) from cell B5, enter the following formula:

• Type equal sign (=) to type formula
• Use CONCATENATE Function, followed by a bracket ()
• After that, use the LEFT Function between brackets.
• Select cell B5 for which cell you want to add and type a comma (,)
• After that, type 5 for the 5th position from the left and close the bracket.
• Type hyphen “-” between double-quotes.
• Use the Right Function followed by a comma
• Then select the B5 Cell and type comma
• Use the LEN Function and select cell B5.
• Type minus 5 (-5) to locate the position of the nth character from the right
• Then close the Brackets.
• Press Enter to see the result.

• Finally drag the Fill Handle to cell C12.

Method 5: VBA to Add Specified Character to All Cells

The following VBA Macro will make your work easier if you want to add specified characters to each cell of a selection.

5.1 VBA: Adding Specific Character at the Beginning of Each Cell

Steps:

• At first, select the range (E5:E12) in which you will add specific text
• After that, hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
• Then click Insert > Module, and paste the following VBA code in the Module Window.
• Add the following VBA Code

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Professor " & c.Value
Next
End Sub

• Then press the F5 key to run this macro and all of the cells will be added to the value Professor before the cell content

5.2 VBA: Adding Specific Text at the End of Each Cell

Steps:

• At first, select the range (B5:B12) in which you will add specific text
• After that, hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
• Then, click Insert > Module, and paste the following VBA code in the Module Window.
• Then add the following VBA Code

Sub AppendToExistingOnRight()
Dim c as range
For each c in Selection
If c.value <> "" Then c.value = c.value & "(USA)"
Next
End Sub

• Finally press the F5 key to run this macro and all of the cells will be added the value “(USA)” before the cell content

Conclusion

Thank you for reading this article. Using these methods, you can easily add characters to cells or specific positions as you want. If you have any questions – Feel free to ask us. We, The Exceldemy Team, are always responsive to your queries.

Related Articles

• How to Find Character in String Excel (8 Easy Ways)
• Excel Formula to Get First 3 Characters from a Cell(6 Ways)
• How to Remove Specific Characters in Excel ( 5 Ways)
• Remove Characters after a Specific Character in Excel (4 Tricks)
• Excel Count Specific Characters in Cell (4 Quick Ways)