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

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


4 Ways to Add a Word in All Rows in Excel

First, get introduced to our dataset which represents some person’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 quotations mark and then will connect a cell using the ampersand.

Steps:

  • Activate Cell C5 and type the following formula in it-
="Mr. "&B5
  • Then hit the ENTER button for the output.

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

Using Ampersand to Add a Word in All Rows

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

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


2. Applying 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-
=CONCATENATE("Mr. ",B5)
  • Later, press the ENTER button for the result.

Applying CONCATENATE Function to Add a Word in All Rows

  • 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 (10 Easy Methods)


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

Excel has the capabilities 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 same 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.

Add a Word in All Rows Using Excel Flash Fill

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

Add a Word in All Rows Using Excel Flash Fill

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 (8 Easy Methods)


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 Beginning

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

Steps:

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

Applying VBA to Add a Word in All Rows

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

Applying VBA to Add a Word in All Rows

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

Applying VBA to Add a Word in All Rows

Code Breakdown:

  • First, I created a Sub
  • Next, declared a variable m as Range.
  • Then, applied the If statement which will check every cell whether it is empty or not. If it’s not empty then the specific word will be added before the value using m.Value.

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

Applying VBA to Add a Word in All Rows

  • 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 (5 Easy Methods)


4.2. Add Word in 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:

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.

Applying VBA to Add a Word in All Rows

Code Breakdown:

  • First, I created a Sub
  • Then declared a variable m as Range.
  • Later, used the If statement which will check every cell whether it is empty or not. If it’s not empty then the specific word will be added after the value using m.Value.

  • 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 (6 Easy Ways)


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo