How to Add Text Suffix with Custom Format in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


Add Text Suffix with Custom Format in Excel: 5 Examples

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.

Sample Dataset to Add Suffix with Custom Format Text in Excel

📌 Steps:

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

Access the Format Cells Window

  • 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.
0.0 "Kg" 

Format Cells Window to Add Suffix with Custom Format Text in Excel

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

Copy Desired Cell Values

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

Paste Values in Formatted Cells

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.

Added Suffix with Custom Format Text in Excel

Read More: How to Add Suffix Without Formula in Excel


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

Now, say we have a 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 in the previous example. Go through the steps below to add the °C suffix to all values with custom format text in Excel.

Sample Dataset to Add Suffix for Temperatures

📌 Steps:

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

Access the Format Cells Window

  • 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.
#.0 "°C"
  • Last but not least, click on the OK button.

Format Cells Window to Add Suffix with Excel Custom Format Text

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

Added Suffix for Temperatures with Excel Custom Format Text

Note:

To insert the degree (°) symbol, hold the Alt key on your keyboard and insert Num 0176. You must insert these digits from the Num keypad.

Read More: How to Add Suffix in Excel


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.

Sample Dataset to Add Suffix Thousands/Millions/Billions

📌 Steps:

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

Access the Format Cells Window

  • 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.
#.00, "Thousands"

Format Cells Window to Add Suffix as "Thousands" with Custom Format Text in Excel

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

Added Suffix Thousands

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

Format Cells Window to Add Suffix as "Millions"

  • 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.
#.00,,, "Billions"

Format Cells Window to Add Suffix Billions with Custom Format Text

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

Added Suffix with Custom Format Text


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.

Sample Dataset to Add Percentage Suffix

📌 Steps:

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

Access the Format Cells Window

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

Format Cells Window to Add Suffix with Custom Format Text in Excel

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

Added Percentage Suffix with Custom Format Text in Excel


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.

Sample Dataset to Add Conditional Suffix

📌 Steps:

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

Access the Format Cells Window

  • 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.
#.00" Increase"; -#.00" Decrease"; 0

Format Cells Window to Add Suffix with Custom Format text in Excel

  • 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 will be pasted into the Status column with the respective suffixes. And, the result would look like this finally.

Added Conditonal Suffix with Custom Format Text in Excel


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.

Sample Dataset to Add Prefix

📌 Steps:

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

Access Format Cells Window

  • 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.
"Tk." 0.0
  • Subsequently, click on the OK button.

Format Cells Window to Add Prefix with Excel Custom Format

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

Added Prefix with Excel Custom Format Text

Read More: How to Add Prefix Without Formula in Excel


Download Practice Workbook

You can download our practice workbook from here for free!


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.



<< Go Back to Suffix and Prefix | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo