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

Dataset Overview

We’ll use the below dataset that represents some people’s names. Our target is to add Mr.  before every name in all the rows.

Method 1 – Using Ampersand to Add a Word in All Rows

• Open your Excel workbook containing the dataset.
• In cell C5, type the following formula:

• Press ENTER to get the output.

• Drag down the Fill Handle icon to copy the formula for other cells.

Now you’ll see that the word Mr. is added before every name in all rows.

Method 2 – Applying the CONCATENATE Function

• In cell C5, insert the following formula:

• Press ENTER to see the result.

• Use the Fill Handle tool to apply the formula to other cells.

The word Mr. will be added before every name.

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

Method 3 – Adding a Word Using Excel Flash Fill

• Manually type the word Mr. before or after the main text in any cell.
• Press ENTER.

• Go to Data > Data Tools > Flash Fill, or use the shortcut Ctrl + E.

Excel will recognize the pattern and add the word Mr. to all rows without using a formula.

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

Method 4 – Using VBA to Add a Word

4.1. Adding a Word at the Beginning

• Copy the text to a new column (e.g., Column C).

• Press ALT + F11 to open the VBA window.
• Insert a module (Insert > Module).

• Write the following code:

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

• Return to your sheet.

• Select the range of cells.
• Open the Macro dialog box (Developer ribbon > Macros).

• Run the specified macro.

• The word Mr. will be added at the beginning of each text.

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

4.2. Adding a Word at the End

• Follow the steps from the previous section.
• Modify the code as follows:

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

• Go back to the sheet.

• Select the range from the dataset.
• Select the macro name as mentioned in the codes.

• Run the macro. The word -SP will be added after each text in the selected range.

Read More: How to Add Text in Excel Spreadsheet

Download Practice Workbook

You can download the practice workbook from here:

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