Suffix and Prefix in Excel: A Complete Guide

In this article, you will learn how to add suffix and prefix in Excel. You will learn to use functions like CONCATENATE, TEXT to add suffixes and prefixes. You will also learn to use features like Format Cells, Power Query and VBA macro to do the same.

Suffixes and prefixes enable users to categorize or classify data by adding a common identifier or label to cell values. By adding prefixes or suffixes, users can facilitate sorting and filtering operations. Users can employ suffixes and prefixes as part of conditional formatting rules to visually highlight or format cells based on specific criteria.

Download Practice Workbook

Download this file to practice with the article.

How to Add Suffix and Prefix in Excel

We will add the prefix Dr. and the suffix PHD to this dataset with various methods.

1. Using Ampersand Operator

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

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

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

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

Read More: Add Suffix in Excel

2. Applying CONCATENATE Function

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

• Finally, use Fill Handle (+) to apply the formula to the rest of the rows.

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

• Finally, use Fill Handle (+) to apply the formula to the rest of the rows.

Read More: Add Suffix Without Formula in Excel

3. Utilizing Format Cells

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

• Finally, click OK to apply the formatting.

• Again to 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, we used this formatting.

• Finally, click OK to apply the formatting.

4. Using Excel Power Query

• First, 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.
• Again, go to Transform tab to add suffix.
• From Text Column group, select Format drop-down menu.
• Finally, select Add Suffix.

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

• You can 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 desired cell reference to import data.
• Press OK to exit.

As you can see, the data with prefix and suffix have been generated.

5. Apply VBA Macro to Add Prefix and Suffix

• We will add prefixes and suffixes to this dataset.

• First, access the Visual Basic Editor by pressing Alt + F11 on your keyboard.
• Next, 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

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

As you can see, the prefixes have been added.

• Again, 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

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

As you can see, the suffixes have been added.

How to Add Both Suffix and Prefix in Excel

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

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

• Now, if you 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.

• Finally, use Fill Handle (+) to apply the formula to the rest of the rows.

Read More: Remove Prefix 91

How to Remove Suffix and Prefix in Excel

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

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

• Finally, use Fill Handle (+) to apply the formula to the rest of the rows.

Read More: Remove Prefix in Excel

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

Conclusion

In conclusion, suffix and prefix in Excel play a significant role in manipulating and modifying cell values. They allow users to add or remove specific text before or after existing data. By using formulas, functions, and formatting options, users can improve data presentation greatly. We hope our article on this will help you vastly ease the process of adding suffix and prefix in Excel.

<< Go Back to Text Formatting | Learn Excel