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.
- Select all the cell in which you want to insert text.
- Click on “Insert Text” on the Professor Excel ribbon.
- 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.
- Click on Insert.
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:
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:
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:
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:
- Select all original cells.
- Press Ctrl + H on the keyboard so that the Find and Replace window opens.
- As “Find what:”, enter “.”
- Because we still want to keep the full stop, we also use this in the “Replace with:” field: “. Cool.”
- 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 Next End Sub