Excel Macros for Dummies by Kusleika Dick;

Author:Kusleika, Dick; [Kusleika, Dick]
Language: eng
Format: epub
Publisher: John Wiley & Sons, Incorporated
Published: 2022-02-22T00:00:00+00:00

To implement this macro, you can copy and paste it into a standard module, as outlined in the first section of this chapter, “Installing Macros.”

Padding Cells with Zeros

Many systems require unique identifiers (such as customer number, order number, or product number) to have a fixed character length. For example, you frequently see customer numbers that look like this: 00000045478. This concept of taking a unique identifier and forcing it to have a fixed length is typically referred to as padding. The number is padded with zeros to achieve the prerequisite character length.

It’s a pain to do this manually in Excel. However, with a macro, padding numbers with zeros is a breeze.

Some Excel gurus are quick to point out that you can apply a custom number format to pad numbers with zeros by going to the Format Cells dialog box, selecting Custom on the Number tab, and entering “0000000000” as the custom format.

The problem with this solution is that the padding you get is cosmetic only. A quick glance at the Formula bar reveals that the data actually remains numeric without the padding (it does not become textual). So if you copy and paste the data into another platform or non-Excel table, you lose the cosmetic padding.

If all your customer numbers need to be ten characters long, you need to pad the number with enough zeros to get it to ten characters. This macro does just that.

As you review this macro, keep in mind that you need to change the padding logic in Step 5 to match your situation.

Sub PadWithZeros()

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.