Software

Ask something
Discussion Locked

EXCEL 2000 Ignores leading zeros.

0 helpful
7 replies

by raycpu

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Can someone please help me "force" Excel 2K to recognise leading zeros in the output of a calculation. I've tried every formatting trick I know,and can display these zeros, but not use them in further calculations: e.g. =left(), =MID(), =find() etc. Manually entered leading zeros are fine, but I'm almost frantic about the result when a formula is applied! With thanks, Raycpu.

7 replies

by danprem

Change the format of the cells (which need to contain the values with leading zeros) into Text. You can now manually add zeros to the front. U can now use left(), mid(), find() on the full value(incl the zeros).

If you want to use the numeric value of those cells in any calculation use the function =VALUE().

Hope this has answered your query!

by raycpu

Sorry danprem, but this isn't practicable as I have 14,000 rows to deal with. With thanks, raycpu.

by dklippert

This will return the first 3 zeros of a number that is formatted with six displayed zeros

=LEFT(TEXT(A1,"00000"),3)

by raycpu

Thanks for your help, but this doesn't work when I involve CRN's in calculations. "Unwanted" zeros are preserved in the first digit position.

by richtee

One way to accomplish this is by custom formatting the cell containing the calculations, etc.

Format | Cells | Number tab | select custom under Category: | In the ?Type:? box enter the following:

000000.###

This specific input will format the cell to return a number with leading zeros up to six places to the left of the decimal point. It will return up to 3 places to the right of the decimal.

You can modify the number of zeros to show before the decimal and the number of places after the decimal to suit your needs. By using this formatting, the number remains a number and can be used in further calculations.

Good Luck, Rich

by raycpu

While this didn't solve my problem competely, it helped me to work around it. Thank you RitchTee!

This discussion is closed to new replies.