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:
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:
Result:
You can download the file related to this article from the link below
See Also:
- Excel Custom Formatting Conditions
- Excel, Custom Formats
- Excel Custom Formatting, 0, # and ? characters
- Excel Custom Formatting Adding Characters and Text
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