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.
How to Add Text to Multiple Cells in Excel: 10 Ways
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 Flash Fill feature to add texts to multiple cells. Say we will insert the text Proverb: at the start 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 Data 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
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. To do 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
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: How to Add a Word in All Rows in Excel
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 the CONCAT function. 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.
Read More: How to Add Text in Excel Spreadsheet
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: ")
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")
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)
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.
- In 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 SubSub 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
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.
Download Practice Workbook
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.
