Zero Supplement and Removal

Problem

Sometimes zero supplement or zero removal is required when you process numerical data, as shown in the following figure.

Solution

1. Select Number from the drop-down list of Format in Cell Element, and set the number of zeros in the input box below.

2. Use formulas to supplement or remove zeros.

3. Use arithmetic operations to supplement or remove zeros.

Zero Supplement Using Format Setting

Select Number from the drop-down list of Format in Cell Element, and enter zeros in the input box below. The number of zeros entered determines the total length of the number after the zero supplement.

Zero Supplement for Numbers Using Formulas

Insert the formula FORMAT(A2,"#00000000") into B2, as shown in the following figure.

Zero Supplement for Dates Using Formulas

When using the MONTH() or DAY() functions, if the month or day is a single-digit value, it will be displayed without a leading zero. For example, January 9th will appear as "1" for the month and "9" for the day, instead of "01" and "09". If the zero-supplemented format shown in the following figure is required, you can use the CONCATENATE() function to prepend a zero to the retrieved month or day, and use the RIGHT() function to extract the rightmost two characters. The formulas are as follows:

1. The formula for months: RIGHT(CONCATENATE('0',MONTH(TODAY())),2)

2. The formula for days: RIGHT(CONCATENATE('0',DAY(TODAY())),2)

Zero Removal Using Format Setting

Select Number from the drop-down list of Format in Cell Element.

Zero Removal Using Formulas

1. The FORMAT() formula: FORMAT(Object from Which Zero is To Be Removed,"#0"), as shown in the following figure

2. The SUM() formula: SUM(Object from Which Zero is To Be Removed), as shown in the following figure

3. The AVERAGE() formula: AVERAGE(Object from Which Zero is To Be Removed), as shown in the following figure

4. The MAX() formula: MAX(Object from Which Zero is To Be Removed), as shown in the following figure

5. The MIN() formula: MIN(Object from Which Zero is To Be Removed), as shown in the following figure

Note:

You are advised not to use formulas 2 to 5 when the data involves grouping, as these formulas might alter the values.

Zero Removal Using Arithmetic Operations

1. Addition: Object from Which Zero is To Be Removed + 0 (for example, = A2 + 0)

2. Subtraction: Object from Which Zero is To Be Removed - 0 (for example, = A2 - 0)

3. Multiplication: Object from Which Zero is To Be Removed * 1 (for example, = A2 * 1)

4. Division: Object from Which Zero is To Be Removed/1 (for example, = A2/1)