Excel Custom Formatting, Removing Digits

Aug 12, 2015 by azurous in Custom Formatting

When working with custom formatting in Excel one of the things you might want to do is remove several digits from being displayed. This can be done using the comma character. I will explain this using Examples. Please note that this comma character is different than the comma character used in custom date formatting explained in the article below:

Excel, Custom Date Formatting

You can also see our article on Removing Characters from Strings in general.

–

Contents

Example 1, Remove Last 3 Digits:

In this example the custom formatting will remove the last 3 digits of the number:

0,

Result:

–

Example 2, Remove Last 6 Digits:

In this example the custom formatting will remove the last 6 digits of the number and add an “M” character to the end of the number:

0,,"M"

Result:

–

Example 3, Remove Last 9 Digits:

In this example the custom formatting will remove the last 9 digits of the number and add a “G” character to the end of the number. The formatting will keep 2 decimal places:

0.00,,,"G"

Result:

–

Example 4:

In this example:

3 digit numbers will be displayed as they are.
4 to 6 digit numbers will be displayed as below:

Unformatted: 132456

Formatted: 132.45k

7 to 9 digit numbers will be displayed as below:

Unformatted: 132456789

Formatted: 132.45M

The formatting used in this example can be seen below:

[<1000]0;[<1000000]0.00,"k";0.00,,"M"

This custom formatting tests the value in the cell if its <1000 and <1000000. For more information about adding conditions to custom formatting please see:

Excel Custom Formatting Conditions

Result:

You can download the file related to this article from the link below

Custom Formatting Comma Character.xlsx