How to Add a Word in All Rows in Excel (4 Smart Methods)

Sometimes we need to add a specific word before or after a text in all rows in Excel. If you input manually in every row then it’s not feasible and that is time-consuming. Excel offers some amazing tricks to perform this operation. You will learn 4 smart methods from this tutorial to add a word in all rows in Excel with sharp steps.

How to Add a Word in All Rows in Excel: 4 Ways

First, get introduced to our dataset which represents some people’s names. Our target is to add “Mr. ” before every name in all rows.

1. Using Ampersand to Add a Word in All Rows

In our very first method, we’ll apply a formula where we’ll use the Ampersand(&)  to add a specific word in every cell. It’s one of the easiest ways. Nothing, just we’ll keep our specific word within quotation marks and then will connect a cell using the ampersand.

Steps:

• Activate Cell C5 and type the following formula in it-

• Then hit the ENTER button for the output.

• Next, drag down the Fill Handle icon to copy the formula for the other cells.

Now have a look, the word is added before every text in all rows.

Read More: 

2. Applying the CONCATENATE Function to Add a Word in All Rows

also, we can add words in any cell using an Excel function. Here, we’ll use the CONCATENATE function for the task.

Steps:

• In Cell C5, insert the following formula-

• Later, press the ENTER button for the result.

• Finally, use the Fill Handle tool to apply the formula for the rest of the cells.

Soon, you will see that the word is added nicely before every text in all rows.

Read More: How to Add Text to Multiple Cells in Excel

3. Add a Word in All Rows Using Excel Flash Fill

Excel has the capability to recognize a pattern. To apply the pattern there is a tool in Excel named Flash Fill. By using it, we can apply the same pattern in every row or column. So, we can use it to insert a specific word in every row. It’s pretty quicker than the other methods. Let’s go forward to see how to apply it.

Steps:

• First, type the word manually before or after the main text and hit the ENTER button.

• After that, click as follows: Data > Data Tools > Flash Fill. Or you can use the shortcut key Ctrl + E.

Now see the magic! Excel recognized the pattern and added the word before every text, needed no formula, no function.

Read More: How to Add Text to Cell Without Deleting in Excel

4. Applying VBA to Add a Word in All Rows

Microsoft Visual Basic for Applications (VBA) can perform any kind of particular operation in Excel. Using simple codes, we can add the same word before or after any text in all rows in Excel.

4.1. Add Word in the Beginning

First, we’ll learn to add a word at the start of a text.

Steps:

• Copy the texts in a new column. I copied it to Column C.

• Then press ALT + F11 to open the VBA window.
• Later, click as follows to insert a module: Insert > Module.

• Next, write the flowing codes in it-

Sub Add_Word_in_the_Begining()
Dim m As Range
For Each m In Selection
If m.Value <> "" Then m.Value = "Mr. " & m.Value
Next
End Sub

• After that go back to your sheet.

• Select the range of cells.
• Finally, click on Macros from the Developer ribbon to open the Macro dialog box.

• Select the specified macro name and press Run.

• Now see, the macro has added the word before every text.

Read More: How to Add Text in the Middle of a Cell in Excel

4.2. Add Word in the End

To add a word after every text, we’ll have to make a little change in our previous codes. Here we’ll add ” -SP” after every text.

Steps:

• Follow the first and second steps from the previous section and then write the following codes in the new module-

Sub Add_Word_in_the_End()
Dim m As Range
For Each m In Selection
If m.Value <> "" Then m.Value = m.Value & "-SP"
Next
End Sub

• Later, go back to the sheet.

• Next, select the range from the dataset.
• Finally, Select the macro name as mentioned in the codes and press Run.

After a while, you will spot that the macro has added the word after every text of the selected range.

Read More: How to Add Text in Excel Spreadsheet

Download Practice Workbook

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to add a word in all rows in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.

Related Articles

• How to Add Text to Cell Value in Excel
• How to Add Text to End of Cell in Excel
• How to Add Text Before a Formula in Excel
• How to Add Text in IF Formula in Excel
• Add Text and Formula in the Same Cell in Excel

<< Go Back to Excel Add Text to Cell Value | Concatenate Excel | Learn Excel