Suffix and Prefix in Excel: A Complete Guide: 5 Methods

Method 1 – Using Ampersand Operator

• Add prefix, select cell D5 write this formula, and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

• To add a suffix, select cell G5, write this formula, and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

Method 2 – Applying CONCATENATE Function

• Add prefix, insert the formula in cell D5 based on the CONCATENATE function and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

• Add suffix, insert the formula in cell G5 based on the CONCATENATE function and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

Method 3 – Utilizing Format Cells

• Select range C5:C9 and press Ctrl + 1 to enter Format Cells dialogue box.

• In the Number tab, select Custom from the Category list.
• In the Type input box write your desired formatting style. As our prefix is Dr. , we used this formatting.

• Click OK to apply the formatting.

• Add suffix, select range D5:D9 and press Ctrl + 1 to enter Format Cells dialogue box.

• In the Number tab, select Custom from the Category list.
• In the Type input box write your desired formatting style. As our suffix is PHD.

• Click OK to apply the formatting.

Method 4 – Using Excel Power Query

• Select range B4:B9.
• Go to Data tab and from Get & Transform Data group, choose From Table/Range.

• Check My table has headers and press OK.

• Power Query Editor window will open up.
• In the Transform tab, select Format from Text Column group.
• Select Add Prefix.

• In the Prefix window, type Dr. and press OK.

• You can see, the prefix has been added to our table.
• Go to Transform tab to add suffix.
• From Text Column group, select Format drop-down menu.
• Select Add Suffix.

• In the Suffix window, type ,PHD and press OK.

• See the prefix has been added to our table.
• Go to Home tab, from Close & Load drop-down menu, and select Close & Load To.

• In the Import Data window, choose Existing worksheet and select the cell reference to import data.
• Press OK to exit.

The data with prefixes and suffixes have been generated.

Method 5 – Apply VBA Macro to Add Prefix and Suffix

• We will add prefixes and suffixes to this dataset.

• Access the Visual Basic Editor by pressing Alt + F11 on your keyboard.
• Create a Module in the Visual Basic Editor from the Insert tab.

• Write this code in the module to add prefixes.

Sub AddPrefix()
    Dim rng As Range
    Dim i As Range
    
    Set rng = Range("B5:B9")
    
    For Each i In rng
        i.Offset(0, 1).Value = "Dr. " & i.Value
    Next i
End Sub

• Press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

The prefixes have been added.

• Create another module from Insert tab.
• Write this code in the module to add suffixes.

Sub AddSuffix()
    Dim rng As Range
    Dim i As Range
    
    Set rng = Range("B5:B9")
    
    For Each i In rng
        i.Offset(0, 2).Value = i.Value & ",PHD"
    Next i
End Sub

• Press F5 or go to Run tab, and from the drop-down menu, click Run Sub/Userform to execute the code.

The suffixes have been added.

How to Add Both Suffix and Prefix in Excel

• Add both prefix and suffix together, insert the formula in cell E5 based on the CONCATENATE function and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

How to Add 0 as Prefix in Excel

1. By Formatting Data as Text

• Select range C5:C9 and go to Home tab.
• In the Number group, from the drop-down menu select Text.

• Write your numbers with 0 as prefix, the cells will show 0 in the front.

2. Using TEXT Function

• Select cell C5 to write this formula using the TEXT function and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

How to Remove Suffix and Prefix in Excel

• Select cell C5 to write this formula using the RIGHT and LEN functions and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

• Select cell C5 to write this formula using the LEFT and LEN functions and press Enter.

• Use Fill Handle (+) to apply the formula to the rest of the rows.

Frequently Asked Questions

1. Is there a way to automatically add a suffix to all values in a column when a condition is met in Excel?

Ans: Yes, you can use the IF function in combination with CONCATENATE or ampersand (&) operator to automatically add a suffix to all values in a column when a condition is met in Excel. For example, if the condition is met in cell A1 and you want to add the suffix “_suffix” to column B, you can use the formula.

2. How do I add a prefix or suffix to the contents of merged cells in Excel?

Ans: In Excel, you cannot directly add a prefix or suffix to the contents of merged cells. However, you can achieve this by unmerging the cells, applying the prefix or suffix to each individual cell, and then re-merging them.

• Unmerge the cells by selecting them. Then, right-click and choose Format Cells.
• Navigate to the Alignment tab, and uncheck Merge cells.
• Apply the prefix or suffix, and finally, merge the cells again.

3. Can I automatically update the prefix or suffix in Excel when the value in another cell changes?

Ans: No, the prefix or suffix in Excel does not automatically update when the value in another cell changes. Prefixes and Suffixes are static and do not have a built-in mechanism to dynamically update based on changes in other cells. To update the prefix or suffix, you would need to manually modify the formula or use VBA macros to automate the process.

Suffix and Prefix in Excel: Knowledge Hub

• Add Text Suffix with Custom Format
• Add Prefix Without Formula
• Add Text Prefix with Custom Format
• Add Prefix to Entire Column

Download Practice Workbook

Download this file to practice with the article.

<< Go Back to Text Formatting | Learn Excel