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.
- 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.
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.
- 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.
- 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.
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.
- 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-
Dim m As Range
For Each m In Selection
If m.Value <> "" Then m.Value = "Mr. " & m.Value
- After that go back to your sheet.
- 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.
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.
- Follow the first and second steps from the previous section and then write the following codes in the new module-
Dim m As Range
For Each m In Selection
If m.Value <> "" Then m.Value = m.Value & "-SP"
- Later, go back to the sheet.
- 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
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.
- 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