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

Here’s an overview of a method where characters are added using Flash Fill.

Download the Practice Workbook

5 Easy Ways to Add Characters in Excel

We have data in existing cells that contain names. We 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 – Using the Ampersand Operator (&) to Add Characters

Steps:

• Click on the first cell of the column where you want the converted names to appear (C5).
• Type an equal sign (=), input or paste the text you want to use as the beginning, such as “Professor ”, then insert an ampersand (&).
• While still in the formula bar, select the cell containing the first name (B5).
• Here’s the resulting formula.

• Press Enter to apply the formula.

• Drag the fill handle to cell C12.

Method 2 – Using the CONCATENATE Function to Add Characters

Case 2.1 – CONCATENATE to Add Characters to the Beginning of all Cells

Steps:

• 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 and select it from the suggested formulas.
• Type the text you want to append (such as “Professor ”) in double quotes, followed by a comma (,).
• Select the cell containing the first name (E5).
• Place a closing bracket.
• In our example, the formula should be:

• Press Enter.
• Drag down the fill handle.

Case 2.2 – CONCATENATE to Add Characters to the End of all Cells

Steps:

• Click on the first cell of the column where you want the converted names to appear (C5 in our example).
• Type an equal sign (=) to start the formula.
• Enter the function CONCATENATE.
• Select the cell containing the first name (B5 in our example).
• Insert a comma followed by the text in double quotes (“ (USA)”).
• Place a closing bracket.
• In our example, the formula should be:

Formula Text

• Press Enter.
• Drag down the fill handle.

Method 3 – Using Flash Fill to Add Characters in Excel

Flash Fill is available in Excel 2013 and newer versions.

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

Steps:

• Click on the first cell of the column where you want the converted names to appear (F5).
• Manually type in the text you want to add (such as “Professor ”), followed by the first value from your list.
• Press Enter.

• There is plus sign in the corner of the C5 cell.
• Right-click and hold on the fill handle, then drag down.

• Release the right-click and select Flash Fill.

• This will copy the same pattern to the rest of the cells in the column.

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

Steps:

• Click on the first cell of the column where you want the converted names to appear (C5).
• Type in the resulting text you need.
• Press Enter.
• Click on cell C5 again.
• Under the Data tab, click on the Flash Fill button.

• This will copy the same pattern to the rest of the cells in the column.

In some cases, you may need to enter the second value as well for Excel to pick up the pattern correctly.

Method 4 – Add Characters in Excel Before or After Specific N-th Character

Steps:

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

• Press Enter to see the result.

• You will have to modify the formula for each cell individually to separate the words since the formula puts the hyphen as the sixth character regardless. For example, using the Fill Handle for C11 results in “Danie-l(USA)” since “Daniel” is six characters long.

Method 5 – VBA to Add Specified Character to All Cells

Case 5.1 – VBA for Adding a Specific Character at the Beginning of Each Cell

Steps:

• Select the range (E5:E12) in which you get the results.
• Press Alt + F11, and Excel will open the Microsoft Visual Basic for Applications window.
• Click on Insert then on Module.
• Paste the following VBA code in the Module Window.

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

• Press the F5 key to run this macro. The cells from C5:C12 will have the text “Professor ” appended to them, and the results shown in E5:E12.

Case 5.2 – VBA for Adding Specific Text at the End of Each Cell

Steps:

• Copy the values from B5:B12 to C5:C12.
• Select the range (C5:C12).
• Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
• Click on Insert then on Module.
• Paste in 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

• Press the F5 key to run the macro.

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)