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

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:
="Mr. "&B5
  • Press ENTER to get the output.

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

Using Ampersand to Add a Word in All Rows

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:
=CONCATENATE("Mr. ",B5)
  • Press ENTER to see the result.

Applying CONCATENATE Function to Add a Word in All Rows

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

Add a Word in All Rows Using Excel Flash Fill

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

Add a Word in All Rows Using Excel Flash Fill

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

Applying VBA to Add a Word in All Rows

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

Applying VBA to Add a Word in All Rows

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

Applying VBA to Add a Word in All Rows

Code Breakdown:

  • A Sub is created
  • A variable m is declared as Range.
  • The If statement is applied 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.
  • Open the Macro dialog box (Developer ribbon > Macros).

Applying VBA to Add a Word in All Rows

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

Applying VBA to Add a Word in All Rows

Code Breakdown:

  • A Sub is created.
  • A variable m is declared as Range.
  • The If statement is use that 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.

  • 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


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

Get FREE Advanced Excel Exercises with Solutions!
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