Md. Sourov Hossain Mithun
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.
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.
- 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.
- 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 (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.
- 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.
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.
- 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:
- 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.
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.
