Skip to main content

How to add text to the beginning or end of all cells in Excel?

Author: Kelly Last Modified: 2024-04-29

Occasionally, you might find yourself needing to prepend or append the same specific text to every cell within a selection. Doing this manually for each cell can be quite tedious and time-consuming. Fortunately, there are several simpler methods to achieve this, allowing you to add the same text to the start or end of all cells in a selection more efficiently.

Add text to the beginning / end of all cells with using Flash Fill

Flash Fill in Excel is a smart tool that automatically fills in data when it detects a pattern. This feature is particularly useful for adding text to cells when you have an example of the desired output.

Suppose you need to prepend "Dr. " to names in column A. Here's how you can use the Flash Fill to achieve this:

  1. Ensure your data is in a column and the next column is empty.

  2. Type the first example with "Dr. " added in the next column's first cell, and hit Enter.

  3. Press Ctrl + E to automatically fill the rest of the cells in the column with "Dr. " prefixed to the names.

    Tip: You can also click on the Data tab on the ribbon, then select Flash Fill to achieve the same result if you prefer using the ribbon interface for Excel operations.

Note: To add specific text after the names, such as appending ", HR" to indicate a human resources department, you can use the same approach:

Add text to the beginning / end of all cells with formulas

To easily insert specific text or characters at the start or end of cells, you can concatenate a specified string with the cell reference using an Excel formula. Suppose you need to prepend "Class A: " to names in column A. Here's how you can apply a formula to achieve this:

  1. Select a cell where you want to output the first name prefixed with "Class A: ".

  2. Input any of these formulas based on your preference and hit Enter to see the first outcome (Here I will use the first formula as an example): 
    ="Class A: "&A2
    =CONCATENATE("Class A: ", A2)
    =CONCAT("Class A: ", A2)
    Tip: In these formulas, "Class A: " is the prefix added to names, and A2 is the first cell containing a name you wish to modify. Feel free to substitute "Class A: " and adjust A2 to suit your specific needs.

  3. Drag the fill handle down to the cells you want to apply this formula.

Notes:

  • For simplicity, place the text you wish to add into a specific cell (such as E2) and then combine the two cells. Tip: The cell address for the prepended text is locked with the $ sign to prevent changing when copying the formula down.

  • To append text to the end of a cell's existing content, simply reverse the formula structure. For example, to add "- Class A" at the end of the content in cell A2, use any of the below formulas: 
    =A2&" - Class A"
    =CONCATENATE(A2, " - Class A")
    =CONCAT(A2, " - Class A")

Add text to the beginning / end of all cells using AI

The AI Aide feature in Kutools for Excel is a significant leap forward in smart data processing. It leverages artificial intelligence to analyze your requests and execute complex tasks with a simple command.

To prepend text like "Dr. " to your selected cells, simply select the cells, click Kutools > AI Aide, and tell Kutools AI Aide: Add "Dr. " before every selected cell. It will instantly understand and present an Execute button. Click it, and the changes will be applied immediately.

Notes:

  • If the result isn’t what you expected and you wish to undo the changes, click the Unsatisfied button to revert your actions.
  • To append text, such as "AA" to the end of all selected cells, adjust your request to Add "AA" after every selected cell.
  • Want to access the AI Aide utility? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Add text to the beginning / end of all cells with VBA

In this section, we'll explore a straightforward VBA method to add specified text directly to the beginning or end of all cells within one or multiple selected ranges.

  1. Select the range(s) where you will add the specified text to the beginning or end of all cells.
  2. Hold down the Alt + F11 keys in Excel to open the Microsoft Visual Basic for Applications window.
  3. Click Insert > Module, and paste the following VBA code in the Module window.
    • VBA: Add specified text to the beginning of cells 
      Sub PrependToSelectedCells()
    Dim c As Range
    For Each c In Selection
        If c.Value <> "" Then c.Value = "EXCL-" & c.Value 
    Next
End Sub

      Note: This code prefixes all selected cells with "EXCL-". Ensure you replace this sample text in the code with the actual text you require before applying it to your worksheet.

    • VBA: Add specified text to the end of cells 
      Sub AppendToSelectedCells()
    Dim c As Range
    For Each c In Selection
        If c.Value <> "" Then c.Value = c.Value & "-XS"
    Next
End Sub

      Note: This macro appends "-XS" to the content of each selected cell. Make sure to replace "-XS" with the specific suffix you wish to append to your cell contents before running the macro in your worksheet.

  4. Press the F5 key to run the macro.

Result

  • If running the first VBA, the value "EXCL-" will be prepended to the contents of all selected cells.

  • If running the second VBA, the value "-XS" will be appended to the contents of all selected cells.

Tip: If you'd like to place the modified values into the right adjacent column rather than adding text directly to the original values, use the codes below:
  • Prepend text to cell contents and place in the next column: 

    Sub PrependToRightOfSelectedCells()
    Dim c As Range
    For Each c In Selection
        If c.Value <> "" Then c.Offset(0, 1).Value = "EXCL-" & c.Value
    Next c
End Sub
  • Append text to cell contents and place in the next column: 

    Sub AppendToRightOfSelectedCells()
    Dim c As Range
    For Each c In Selection
        If c.Value <> "" Then c.Offset(0, 1).Value = c.Value & "-XS"
    Next c
End Sub

Add text to the beginning / end of all cells with Kutools for Excel

Kutools for Excel's Add Text tool is designed to efficiently handle the addition of specified text at the beginning or end of each cell across one or multiple selections. Furthermore, this tool excels in more detailed tasks such as:

  • Adding text before or after specific segments within the cell content.
  • Inserting text at a designated position.
  • Wrapping text around existing content by adding prefixes and suffixes simultaneously.
  • Appending text specifically before upper and/or lower-case letters.
  • And more specialized text manipulation needs...

Once you have Kutools for Excel installed, select one or multiple ranges where you want to add specified text, and click Kutools > Text > Add Text. In the Add Text dialog box, please do as follows:

  1. Type the text you want to add.
  2. Select the desired option for where to place the text. You can preview the result in the preview pane.
  3. Hit OK.

Tips:
  • In the provided screenshot, " 2024" has been entered in the text box with the After last character option selected. You can see a preview of how the text will appear in each cell before applying the changes to your selected range(s).
  • If you check the Skip non-text cells option, the tool will not add the specified text to cells that contain non-text content.

Note: Want to access the Add Text utility? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Visually add text to the beginning / end of all cells using custom formatting

Custom formatting in Excel is a powerful feature that allows you to visually modify cell displays without actually altering the cell's original content. It's particularly useful for adding units or identifiers that are essential for interpretation but should not interfere with calculations.

Suppose you have a column of numbers representing weights that you want to display with the unit "kg" appended to each number without changing the actual numbers for calculation purposes. Follow the steps below:

  1. Select the cells that contain the weights. You can select a range, an entire column, or specific cells within a spreadsheet.

  2. Hold down the Ctrl + 1 keys to open the Format Cells dialog. Alternatively, you can right-click the selected cells and choose Format Cells from the context menu.
  3. In the Format Cells dialog, on the Number tab, please:
    1. Select Custom from the category list on the left.
    2. In the Type input box under Custom, enter the following format code: #" kg". You can then preview the new format in the Sample box to see how it looks before applying.
    3. Click OK.

Result

Now, each number in your selection will display with "kg" appended, while the actual values in the cells remain unchanged, so any calculations that involve these cells will still use the pure numerical data without the "kg" text.

Notes:

  • The format code #" kg" visually appends " kg" to all cells containing numbers, you can customize it as follows:
    • Replace the kg within the quotation marks with the text you want to add.
    • If your cells contain text and you want to append specific text to them, replace # (number sign) with @ ("at" sign) in the format code.

  • To visually add text to the beginning of cells, precede the # in your format code with the text. For instance, entering "text "# in the format box will prepend "text " to all numeric cells.

Insert text after Nth character within every cell

In this section, we'll explore two methods to insert specific text after the Nth character in each cell: one using a REPLACE formula and the other with a versatile tool. As an example, we'll show how to insert a hyphen (-) after the 5th character.

Insert text after Nth character with a REPLACE formula

To insert specific text or characters after the Nth character in cells with the REPLACE function, we'll follow the pattern described below.

=REPLACE(cell, N+1, 0, "text")

The logic behind this formula is to replace 0 (no) characters of the cell at the (N+1)th position with "text". For instance, to insert a hyphen (-) after the 5th character, I will use the below formula:

=REPLACE(A2, 6, 0, "-")

Insert text after Nth character with Kutools for Excel

By applying Kutools for Excel's Add Text function, you can add specified text not only to the beginning or end of the cells but also to a specified position within the cells.

To insert a hyphen (-) after the 5th character, select one or multiple ranges where you want to insert a hyphen, and click Kutools > Text > Add Text. In the Add Text dialog box, please do as follows:

  1. Type a hyphen (-) in the text box.
  2. Select the Specify position option, and enter 5 to insert the hyphen after the 5th character. You can preview the result in the preview pane.
  3. Hit OK.

Note: Want to access the Add Text utility? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Append text before / after specific characters

In this section, we'll explore two methods for appending text before or after specific characters within Excel cells: one using a SUBSTITUTE formula and another with a versatile tool. For illustration, we'll demonstrate how to insert "(EO)" before and after the hyphen (-) in cell contents.

Insert text before / after specific characters with a SUBSTITUTE formula

To insert text or characters before or after specific characters in cells with the SUBSTITUTE function, we'll follow the patterns described below.

  • Prepend text before specific characters ("anchor_text"):

    =SUBSTITUTE(cell, "anchor_text", "added_text & anchor_text", 1)

  • Append text after specific characters ("anchor_text"):

    =SUBSTITUTE(cell, "anchor_text", "anchor_text & added_text", 1)

The logic behind the formulas is to replace "anchor_text" within the cell with "added_text & anchor_text" or "anchor_text & added_text", so as to insert "added_text" before or after "anchor_text".

For instance, to insert "(EO)" before the hyphen (-) in cell contents, use the below formula:

=SUBSTITUTE(A2, "-", "(EO)-", 1)

Tips:
  • The number 1 in the SUBSTITUTE formula tells Excel to replace only the first occurrence of the hyphen (-) in cell A2. If there are multiple hyphens and you wish to replace a different one, adjust this number to match the specific instance you're targeting. To add "EO" before every hyphen, remove the instance number entirely: 
    =SUBSTITUTE(A2, "-", "(EO)-")
  • To insert "(EO)" after the hyphen (-), use the below formula: 
    =SUBSTITUTE(A2, "-", "-(EO)", 1)
Insert text before / after specific characters with Kutools for Excel

Kutools for Excel's Add Text feature allows you to easily add text before or after specific characters within the cells.

To insert "(EO)" before the hyphen (-) in cell contents, select one or multiple ranges where you want to make this modification, and click Kutools > Text > Add Text. In the Add Text dialog box, please do as follows:

  1. Type (EO) in the text box.
  2. Select the Before text option, and enter a hyphen (-) in the corresponding input box. You can preview the result in the preview pane.
  3. Hit OK to insert (EO) before the hyphen.

Note: Want to access the Add Text utility? Download Kutools for Excel now! Beyond this, Kutools boasts a myriad of 300+ other features and offers a 30-day free trial. Don't wait, give it a try today!

Above is all the relevant content related to removing macros in Excel. I hope you find the tutorial helpful. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials.