Excel Custom Formatting, Removing Digits

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:

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:

Excel Custom Formatting, Remove 3 digits


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:

Excel Custom Formatting, Remove 6 digits


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:

Excel Custom Formatting, Remove 9 digits


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:

Result:

Excel Custom Formatting, Example 4

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

See Also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *