How to Add Text to Multiple Cells in Excel (10 Easy Methods)

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.


Download Practice Workbook

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


10 Ways to Add Text to Multiple Cells in Excel

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

How to Add Text to Multiple Cells in Excel

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 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.

How to Add Text to Multiple Cells in Excel Using Flash Fill Feature

  • 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.

How to Add Text to Multiple Cells in Excel Using Flash Fill Feature

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

How to Add Text to Multiple Cells in Excel Using Flash Fill Feature

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.

How to Add Text to Multiple Cells in Excel Applying Highlighting Cells

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

How to Add Text to Multiple Cells in Excel Applying Highlighting Cells

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

How to Add Text to Multiple Cells in Excel Applying Highlighting Cells


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.

How to Add Text to Multiple Cells in Excel Utilizing Find and Replace Feature

  • 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.

How to Add Text to Multiple Cells in Excel Utilizing Find and Replace Feature

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

How to Add Text to Multiple Cells in Excel Utilizing Find and Replace Feature

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

How to Add Text to Multiple Cells in Excel Utilizing Find and Replace Feature

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

Utilizing Find and Replace Feature

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.

How to Add Text to Multiple Cells in Excel Utilizing CONCATENATE Function

  • 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.

How to Add Text to Multiple Cells in Excel Utilizing CONCATENATE Function

  • 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.

How to Add Text to Multiple Cells in Excel Utilizing CONCATENATE Function

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 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.

How to Add Text to Multiple Cells in Excel Using CONCAT Function

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


Similar Readings


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.

How to Add Text to Multiple Cells in Excel Implementing Ampersand operator


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.

How to Add Text to Multiple Cells in Excel Applying REPLACE Function


 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.

How to Add Text to Multiple Cells in Excel Using SUBSTITUTE Function


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.

Employing TEXTJOIN Function


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.

Applying VBA Code

  • 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.

Applying VBA Code

  • 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.

Applying VBA Code

  • 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.

Applying VBA Code

  • 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.

Applying VBA Code

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

Applying VBA Code

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.

Practice Section


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.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo