How to add to all cells in Excel

Need to learn how to add text to multiple cells in Excel? If you are looking for such unique tricks, you’ve come to the right place. Here, we will take you through 10 easy and convenient methods of adding text to multiple cells in Excel.

Table of Contents

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Adding Text to Multiple Cells.xlsm

10 Ways to Add Text to Multiple Cells in Excel

Suppose, you have a List of Proverbs. This dataset includes ten popular English Proverbs.

Now, we want to add text with these Proverbs. In most cases, we’ll add Proverb: at the beginning of each text string in the B5:B14 range.

1. Using Flash Fill Feature to Add Text to Multiple Cells in Excel

We can use the wonderful feature to add texts to multiple cells. Say we will insert the text Proverb: at the starting of every Proverb in cells in the B5:B14 range. Let’s go through the procedure below.

📌 Steps

• At first, select cell C5.
• Then, write down the Proverb: A cat has nine lives manually in the cell.

• Secondly, go to the tab.
• After that, select the Flash Fill feature on the Data Tools group. Also, you can use the keyboard shortcut CTRL+E to do the same.

• Magically, all the remaining cells in Column C get filled based on the pattern of cell C5.

Read More: How to Add Text to Beginning of Cell in Excel (7 Quick Tricks)

2. Highlighting Cells to Add Text

In Excel, you can enter the same text into multiple cells in a wink using the highlight option. Let’s explore the method step by step.

📌 Steps

• Firstly, select cells in the B5:B14 range. Now, they become highlighted.

• Then, write down A cat has nine lives. manually. At this moment, it’s showing in cell B5.

• Finally, press CTRL+ENTER to see the magic.
• All the cells get filled with the same text string.

3. Utilizing Find and Replace Feature

The fundamental concept behind this technique is to substitute new text for the original text found in the cells. For doing this, we’ll use the Find and Replace feature of Excel. Follow the steps below.

📌 Steps

• Firstly, select cells in the B5:B14 range.
• Secondly, press CTRL+C to copy them.
• Then, select cell C5.
• After that, press CTRL+V to paste them into the selected range.

• At this moment, press the CTRL key followed by the H key on your keyboard.
• Suddenly, the Find and Replace dialog box opens.
• Now, give a full stop ( . ) in the box of Find what.
• Besides, write down. TRUE in the box of Replace with.
• Lastly, select the Replace All button.

• Instantly, a warning box appears.
• Now, click OK.

• It returns us to the Find and Replace dialog box again.
• Later, select the Close button.

Thus, we’ve successfully added a text TRUE at the end of the text string in each cell.

Read More: How to Add Text to End of Cell in Excel (6 Easy Methods)

4. Utilizing CONCATENATE Function

You can use the CONCATENATE function to add text to multiple cells in Excel. Follow the steps below.

📌 Steps

• At the very beginning, select cell C5.
• Then, write down the formula below.

=CONCATENATE("Proverb: ",B5)

Here, B5 represents the cell reference for the first proverb in the column Original Data.

• After that, press ENTER.

• At this moment, move the cursor to the bottom-right corner of cell C5 to get a plus (+) sign like the image below. It’s called the Fill Handle tool.
• Now, drag the Fill Handle tool down to cell C14 to get the other results.

• By our previous action, we could successfully copy the formula to these remaining cells in order to get the results.
• Now, the worksheet CONCATENATE looks like the one below.

Read More: Combine Text and Formula in Excel (4 Simple Ways)

5. Using CONCAT Function to Add Text to Multiple Cells in Excel

In our fifth method, we’ll use a modernized replacement of the CONCATENATE function, which is . It’s available on Excel 2019, Excel 365, and Excel Online.
If you are using any of the versions stated above, you obviously can use this method. Follow the steps carefully.

📌 Steps

• Firstly, select cell C5.
• Then, type in the formula below.

=CONCAT("Proverb: ",B5)

In this case, B5 serves as the cell reference for the 1st proverb in the column Original Data.

• Later, press the ENTER key.

Thus, we can get the other results by using the Fill Handle tool.

Similar Readings

• Combine Text and Number in Excel (4 Suitable Ways)
• How to Add Text in Excel Spreadsheet (6 Easy Ways)
• Add a Word in All Rows in Excel (4 Smart Methods)
• How to Add Text Labels in Excel Chart (4 Quick Methods)

6. Implementing Ampersand Operator

We can use an ampersand (&) operator to merge text strings effortlessly in Excel. Let’s see how we use it to add text in multiple cells.

📌 Steps

• Initially, select cell C5.
• Secondly, put down the formula below.

="Proverb: "&B5

• Next, hit the ENTER key.

7. Applying REPLACE Function

We can also use the REPLACE function to add text in multiple cells. Let’s go through the process below.

📌 Steps

• At first, select cell C5.
• Secondly, put down the formula below.

=REPLACE(B5,1,0,"Proverb: ")

⚙ Formula Breakdown
The REPLACE function is used to replace a part of a text string with a different text string. It returns with the new text string within which new and replaced text or word is present.
The syntax of this function is: REPLACE(old_text, start_num, num_chars, new_text)
Here, B5 is the old_text, which means the text within which a part has to be replaced. In this case, it is A cat has nine lives.
1 is the start_num, which means the starting number of the character of the part that has to be replaced. In this situation, it is A.
0 is the num_chars, which means the number of characters that have to be replaced with a new text. In this position, it is null. So, nothing will be replaced. Rather, it will add something at the beginning of the old_text.
“Proverb: “ is the new_text, which means the text that has to be added by replacing the old one in the text string.

• Finally, press ENTER.

 8. Using SUBSTITUTE Function

If you want to add text in multiple cells in Excel, you can use the SUBSTITUTE function for this purpose. Let’s go through the procedure below for a better understanding.

📌 Steps

• Primarily, select cell C5.
• Then, write down the formula below.

=SUBSTITUTE(B5,".",". TRUE")

⚙ Formula Breakdown
The SUBSTITUTE function swaps out current text with the latest text in a text string. The syntax for this function is: SUBSTITUTE(text, old_text, new_text)
Here, B5 is the text, which means the text within which a part has to be replaced. In this case, it is A cat has nine lives.
“.” is the old_text, which means the text to be replaced.
“. TRUE” is the new_text, which means the text that has to be added by replacing the old one in the text string.

• Lastly, hit the ENTER button.

9. Employing TEXTJOIN Function

Also, we can try out the problem with the TEXTJOIN function. Let’s go through the procedure below to be able to do that.

📌 Steps

• Firstly, select cell C5.
• After that, paste the formula below.

=TEXTJOIN(": ",TRUE,"Proverb",B5)

⚙ Formula Breakdown
The TEXTJOIN function returns a text string by joining all the given texts separated by the delimiter. The syntax of this function is: =TEXTJOIN(delimiter,ignore_empty,text1,…)
Here, “: ” is the delimiter, which is the separator of the concatenated texts. Notice carefully that there is a blank space after the colon.
TRUE is the ignore_empty. It tells whether to ignore the empty cells in the range or not. Here, we’ll ignore the empty cells.
“Proverb” is the text1, which is the first text string to be joined.
B5 is the text2, which is the second text string to be joined. In this case, it is A cat has nine lives.

• Lastly, press ENTER.

10. Applying VBA Code to Add Text to Multiple Cells in Excel

Applying the VBA code is always an amazing alternative. Follow our steps below.

📌 Steps

• Firstly, press the ALT+F11 key.

• At this instance, the Microsoft Visual Basic for Applications window will open.
• Then, go to the Insert tab.
• After that, select Module from the options.

• It opens the code module where you need to paste the code below.
• Besides, save the file as an Excel Macro-enabled workbook.

Sub add_text()
Dim inpt As Range
Dim c As Range
Set inpt = Application.Selection
For Each c In inpt
c.Offset(0, 1).Value = "Proverb: " & c.Value
Next c
End Sub

Code Breakdown
Sub add_text(): The lines of code are placed in this. You can use it recalling anywhere throughout the program.
Dim inpt As Range and Dim c As Range both are variables.
Set inpt = Application.Selection: It means when we select a range of cells, they are set as the inpt variable.
For Each c In inpt: This means the below operations are applicable for each cell in the inpt variable.
c.Offset(0, 1).Value = “Proverb: ” & c.Value: By using Offset(0,1), we move one cell to the right ( B5 to C5). And the value in cell C5 will be the value of cell B5 concatenated with “Proverb: “ at the beginning.
Next c: It authorizes the application of the same formula in the next cell in the inpt range.
End Sub: It indicates the end of the macro.

• Now, return to the worksheet VBA.
• After that, select cells in the B5:B14 range.
• Then, go to the Developer tab.
• Later, select Macros on the Code group.

• Thus, it will open up the Macro dialog box.
• Here, we can see the macro add_text selected, which we’ve created just now.
• Then, click on the Run button.

Hence, we can see the blank cells filled automatically with our desired results.

Read More: How to Add Text to Cell Value in Excel (4 Easy Ways)

Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

Conclusion

This article provides easy and brief solutions to adding text to multiple cells in Excel. Don’t forget to download the Practice file. Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website ExcelDemy to explore more.

How do you add data to all cells in Excel?

How do you add numbers to all cells?

How do I add the same number to multiple cells in Excel?