When working with Excel, we come across numerous types of data. Now, for large calculations, it would be easier to understand if we could get the units with values at each cell. But, Excel doesn’t show units by default. In this case, we have to add the respective units as suffixes with Custom formatting. In this article, I will show you 5 practical examples of adding a Suffix in Excel with Custom Format text.

Download Practice Workbook

You can download our practice workbook from here for free!

5 Examples of Adding Text Suffix with Custom Format Feature in Excel

1. Adding a Text Suffix (e.g. Kg) for Mass

Suppose, we have a dataset containing multiple weight values. But, there is no unit with those values. Now, we want to add the unit as “Kg” as suffix to the values. So, we have created a column named Weight (Without Unit) and another column named Weight (With Unit). Follow the steps below to add the unit as a suffix to the values with custom format text.

Steps:

• First and foremost, select the cells C5:C12.
• Subsequently, press Ctrl + 1 on your keyboard.

• As a result, the Format Cells window will appear.
• Following, go to the Number tab here >> choose Custom option from the Category: pane >> insert formula below in the Type: text box >> click on the OK button.

• Afterward, select the cells B5:B12 >> right-click on your mouse >> click on the Copy option from the context menu.

• Afterward, right-click on cell C5 and choose the option Paste Values form the context menu.

Thus, you would see all the required values are copied into the new column and all of them have the suffix Kg with the values. And, the output should look like this now.

2. Adding a Text Suffix (e.g. °C) for Temperature

Now, say we have dataset containing temperature values. Now, we need to add a suffix °C with the temperature values. So, we have created two columns named Temperature (Without Unit) and Temperature (With Unit) just like the previous example. Go through the steps below to add °C suffix to all values with custom format text in Excel.

Steps:

• At the very beginning, select cells C5:C12.
• Following, press Ctrl + 1 on your keyboard.

• Consequently, the Format Cells window will appear.
• Subsequently, go to the Number tab and click on the Custom option from the Category: pane.
• Following, write the formula below in the Type: text box.

• Last but not least, click on the OK button.

• Now, copy the cells B5:B12 and Paste Values into C5:C12 cells similar to the previous example.

Consequently, you will get your desired result with units as suffix. For instance, the result should look like this.

3. Add Text Suffixes for Thousands/Millions/Billions with Custom Format

At this time, we have a dataset containing numbers. But, we need to express those numbers in thousands and millions and billions with the respective units as suffixes in three different columns. Follow the steps below to do this.

Steps:

• First, for numbers in thousands, select cells C5:C12 and press Ctrl + 1 on your keyboard.

• As a result, the Format Cells window will appear.
• Subsequently, go to the Number tab here and choose the Custom option from the Category: pane.
• Following, insert the formula below in the Type: text box and click on the OK button.

• As a result, if you copy the cells B5:B12 and Paste Values into C5:C12, you would get the numbers in thousands and with suffix Thousands.

• Now, follow the same procedures for Millions. But, change the formula like below in the Type: text box.

• Thus, you would get the numbers in millions along with the suffix.
• Now, for billions, follow the same procedures again but change the formula like below in the Type: text box.

Thus, you would get the numbers in Billions with proper suffix. And, the whole picture of the outcome would look like this now.

4. Adding Percentage Suffix (%) with Custom Format

At this time, say, we have a dataset with several decimal numbers. But these numbers actually represent percentage values. So, we need to add percentage signs as suffix at the end of the values. Follow the steps below to accomplish this.

Steps:

• Initially, select C5:C12 cells and press Ctrl + 1 on your keyboard.

• Consequently, the Format Cells window will appear.
• Now, go to the Number tab here >> choose the Custom option from the Category: pane >> Type 0% in the Type: text box >> click on the OK button.

• Now, copy the cells B5:B12 and Paste Values into C5:C12 cells.

As a result, you will see that all the values are transformed into percentages with proper suffixes. For instance, the output would look like this.

5. Adding a Conditional Suffix with Custom Format

Now, we want to add suffix to values based on conditions. Say, we have a dataset of positive and negative numbers. Now, from these numbers signs, we want to add suffix as Increase for positive numbers and Decrease for negative numbers. Follow the steps below to achieve this target.

Steps:

• First and foremost, select the cells C5:C12.
• Next, press Ctrl + 1 on your keyboard.

• As a result, the Format Cells window will appear.
• Following, go to the Number tab here >> choose the Custom option from the Category: pane >> insert the following formula in the Type: text box >>> click on the OK button.

• Now, copy your desired values from cells B5:B12 and Paste Values into cells C5:C12.

As a result, you will see that the numbers would be pasted into the Status column with the respective suffixes. And, the result would look like this finally.

How to Add a Prefix with Custom Format Feature In Excel

Just like adding suffixes, you can also add prefixes to your values with custom format text in Excel. Say, we have a dataset of several profit values. Now, we need to add the prefix “Tk.” with all the values. Follow the steps below to achieve this desired result.

Steps:

• At the very beginning, select the cells C5:C12.
• Following, press Ctrl + 1 on your keyboard.

• As a result, the Format Cells window will appear.
• Now, go to the Number tab here and choose the option Custom from the Category: pane.
• Following, insert the formula below in the Type: text box.

• Subsequently, click on the OK button.

• At this time, just like the previous methods, copy the values of cells B5: B12 and Paste Values into C5:C12 cells.

Thus, you would find the desired prefix in all values. And, the final output would look like this.

Conclusion

So, in this article, I have shown you 5 practical examples of custom format text suffix in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

And, visit ExcelDemy to learn more things about Excel! Have a nice day! Thank you!

The post How to Add Text Suffix with Custom Format in Excel (5 Examples) appeared first on ExcelDemy.