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.


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-
="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: 


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


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.

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


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.

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


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:

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 create a Sub
  • Then declare a variable m as Range.
  • Later, use 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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo