Bulk Insert Text: How to Add Text to Many Existing Excel Cells at Once

You have a couple (or many) cells with text in it. Now, you want to insert more text to them. Either at the beginning, in the middle or at the end. Here is how to easily do that!

Method 1: The fastest way to bulk insert text

Because it is the fastest and most convenient way, we go with this method first.

Use Professor Excel Tools to easily insert text - and select from further options (subscript, superscript or the position of where to insert the text).
Use Professor Excel Tools to easily insert text – and select from further options (subscript, superscript or the position of where to insert the text).
  1. Select all the cell in which you want to insert text.
  2. Click on “Insert Text” on the Professor Excel ribbon.
  3. Type your text and select further options (for example, you can specify the position (add the text in the beginning of the existing text, at the end or at a character position). Also, choose if you want o insert it as normal text, subscript or superscript.
  4. Click on Insert.

Click here to learn more about Professor Excel Tools. Or click here to start the download.

Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Method 2: Use a string formula to combine two text parts

The second method is based on formulas. You can combine two text strings with the & sign (actually, there are four different ways to concatenate text, but using the & sign is usually the fastest).

So, let’s see how it works:

Concatenate the two text parts with the & sign.
Concatenate the two text parts with the & sign.

In this example, you have existing in cells B2 to B5. You want to add the word “Cool.” to it. So, the formula in cell C2 is:

 =B2&" Cool."

Please note that I have added a space before the word cool (on purpose…). The reason is that between the previous full stops and the word cool should have a space.

You can now copy the new cell (range C2 to C5). Paste it using paste special on top of the existing cells as values if you want to fully replace the original text cells.

Method 3: Try a workaround to insert text with the Find & Replace function

Admittedly, this method is a little bit trial and error. If it works depends on your existing cells. The main idea is to replace text from the original cells with new text.

Let’s go back to our original example. You again want to add the word “Cool.” to your existing cells:

Insert text with the Find and Replace function.
Insert text with the Find and Replace function.

In this case, we are lucky that all existing cells end with a full stop. We can use this to replace it the following way:

  1. Select all original cells.
  2. Press Ctrl + H on the keyboard so that the Find and Replace window opens.
  3. As “Find what:”, enter “.”
  4. Because we still want to keep the full stop, we also use this in the “Replace with:” field: “. Cool.”
  5. Click on Replace All.

If the result is not as expected, you can simply undo the replace process (press Ctrl + Z on the keyboard).

Method 4: Bulk insert text with a VBA macro

If you feel comfortable to use a short VBA macro, you can copy and paste the following code into a new VBA module. Please refer to this article for help.

Replace the word ” Cool.” with your text to add at the end. Also, you can set a text to insert in the beginning. Then, place the cursor within these lines of code and press F5 on the keyboard.

Sub bulkInsertText()
    Dim textToInsertAtTheEnd As String, textToInsertAtTheBeginning As String
    'Replace "Cool" with your text to insert at the end
    textToInsertAtTheEnd = " Cool."
    textToInsertAtTheBeginning = ""
    For Each cell In Selection
        If cell.HasFormula = False Then
            cell.Value = textToInsertAtTheBeginning & cell.Value & textToInsertAtTheEnd
        End If
End Sub

Image by Gerd Altmann from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *