Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to display zero at the left hand of a code in a cell say 045
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to display zero at the left hand of a code in a cell say 045
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 -- macropod [MVP - Microsoft Word] "Marie" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to display zero at the left hand of a code in a cell say 045
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 "Marie" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to display zero at the left hand of a code in a cell say 045
\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. "Marie" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to click on a cell and have the content of the cell display in a different cell | Excel Worksheet Functions | |||
Find the cell value in excel by using vb code | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |