How to Add Characters in Excel (5 Ways)

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.


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.

5 Easy Ways to Add Characters in Excel


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.

Step 1:

  • 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 (&).
="Professor "& B5

Step 2:

  • Select the cell containing the first name (B5).
  • Press Enter to see the result.
  • Drag to AutoFill the cells.

Method 1: Ampersand Operator (&) to add characters to cell


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.

Step 1:

Click on the first cell of the column where you want the converted names to appear (F5).

Step 2:

  • Type equal sign (=) to type formula.
  • Enter the function CONCATENATE

Step 3:

  • 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

Formula Text

=CONCATENATE("Professor ", E5)

Method 2: CONCATENATE Function to add characters to cell

Step 4:

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

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.

Step 1:

  • Click on the first cell of the column where you want the converted names to appear (C5 in our example).

Step 2:

  • Type equal sign (=) to type formula.
  • Enter the function CONCATENATE.
  • 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

=CONCATENATE(B5, " (USA)")

Method 2: CONCATENATE Function to add characters to cell

Step 3:

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

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

Step 1:

  • 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

Step 2

  • Press Enter.
  • Click on cell F5 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).

 

Method 3: Flash Fill to Add Character

This will copy the same pattern to the rest of the cells in the column… in a flash!

Method 3: Flash Fill to Add Character

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

Step 1:

  • 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

Step 2:

  • Press Enter.
  • Click on cell C5 again.
  • Under the Data tab, click on the Flash Fill button

Method 3: Flash Fill to Add Character

This will copy the same pattern to the rest of the cells in the column… in a flash!

Method 3: Flash Fill to Add Character


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. The syntax for that method is,

=CONCATENATE(LEFT(cell, n), “text”, RIGHT(cell, LEN(cell) -n))

Where,

  • LEFT(cell, n)= position of nth character from the left you want to add character.
  • LEN(cell) -n)= Total number of characters minus nth character.
  • RIGHT(cell, LEN(cell) -n))= position of nth character from the right side.
  • CONCATENATE(LEFT(cell, n), “text“, RIGHT(cell, LEN(cell) -n)) = Add two halves into one using CONCATENATE Function

For example, you want to add a (-) after the 5th character between the words James and (USA) from cell B5

Formula Text

=CONCATENATE(LEFT(B5, 5), "-", RIGHT(B5, LEN(B5) -5))

Step 1:

  • Type equal sign (=) to type formula
  • Use CONCATENATE Function, followed by a bracket ()

Step 2:

  • Use the LEFT Function between brackets.
  • Select cell B5 for which cell you want to add and type a comma (,)
  • Type 5 for the 5th position from the left and close the bracket.
  • Type hyphen “-” between double-quotes.

Step 3:

  • Use the Right Function followed by a comma
  • 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
  • Close the Brackets.

Step 4:

  • Press Enter to see the result

Method 4: Add Text before/after Specific Nth Character


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

Step 1:

  • Select the range (E5:E12) in which you will add specific text

Step 2:

  • Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  • 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

Method 5: VBA to Add Specified Character to All Cells

Step 3:

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

Method 5: VBA to Add Specified Character to All Cells

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

Step 1:

  • Select the range (B5:B12) in which you will add specific text

Step 2:

  • Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
  • Click Insert > Module, and paste the following VBA code in the Module Window.
  • 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

Method 5: VBA to Add Specified Character to All Cells

Step 3:

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

Method 5: VBA to Add Specified Character to All Cells


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.


You May also be interested in

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)

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo