how to display zero at the left hand of a code in a cell say 045

[Guest]

In a cell I need to show codes with a zero at the left, how can do this
without having to format it as text first?

 

[macropod]

Hi Marie,

Use Format|Cells|Number, choose 'Custom' and input as many 0s as required
for padding into the 'Type' box. For example, if you input:
0000
and the cell contains:
1, Excel displays 0001
123, Excel displays 0123

Cheers

 

[Arvi Laanemets]

Hi

1. You can format the range as Custom "00000" (the number of 0's in format
string determines the length, the number is padded to). Actually the entry
remains a number - the leading 0's are only displayed.

2. You can format the range as text before you enter any values. Now you can
enter all values with leading 0's.

3. You can start all entries with an apostrophe, like '00001 - such entries
are interpreted by Excel as text, whatever the cell real format would be.

4. You can convert numeric entries into text ones, using TEXT function, like
=TEXT(A1,"00000"). Later you can convert them to values, usung paste
special, and replace original values with them.

5. you can convert numeric entries into text ones, using concatenation and
RIGHT function, like
=RIGHT("00000" & A1,5). NB! Unlike all other solutions, when some entry
contains more numbers as the length you determined in formula, the part of
it is cut off. P.e.
=TEXT(1234,"000") returns "1234", but
=RIGHT("000" & 1234,3) returns "234"


Arvi Laanemets

 

[Guest]

\0 will give you a leading zero but you must follow it with as many # signs
as you think you might need. It will not display unwanted zeroes. 025 will
display as 025 and not 000025.