Leading Zero

R

Rumy

I have a cell where the number has a leading zero ( Ex - 09123456789 ). But
when i select the cell , on the formula bar the leading zero is not showing (
it shows 9123456789 ) how do i change this format. I want to see the leading
zero in the formula bar !!!
 
M

Max

What you see within the formula bar of the cell is always the real, true
value. If you want the leading zero to be included as part of the real value,
you must either format that cell as TEXT first, then key-in the leading zero
(or precede the leading zero input with an apostrophe). Any worth? hit the
YES below
 
P

Pete_UK

If you type that number in, then type it as '09123456789, i.e. with a
leading apostrophe. The apostrophe will not show in the cell.

If you have a lot of these values, say in column A, then you could use
column B with a formula like this:

=TEXT(A1,"00000000000")

if your values are all 11 digits long, and copy this down. You could
then fix the values in column B, and delete column A.

Hope this helps.

Pete
 
D

David Biddulph

If you want to see the leading zero in the formula bar, you'll need to
pre-format the cell as text, or precede your "number" by an apostrophe. If
you do this, the "number" will not be treated as a number, but as text.
 
R

Rumy

Thank U sir. Great to find someone in S'pore answering my question.:) ok i
have over 5000 rows. So i cant manually type the zero. Is there a easy way to
put that zero in all the cells ,plz ? I change to text already. Thkz
 
M

Max

Assume your source data is running in A2 down
Put in B2: ="0"&A2
Copy down all the way to B5000. Then copy col B, overwrite col A with a
paste special as values. Clean up by clearing col B. Success? hit the YES
below.
 
D

David Biddulph

See Pete_UK's reply.
--
David Biddulph

Rumy said:
Thank U sir. Great to find someone in S'pore answering my question.:) ok i
have over 5000 rows. So i cant manually type the zero. Is there a easy way
to
put that zero in all the cells ,plz ? I change to text already. Thkz
 
D

David Biddulph

I think that the OP is probably using the MS shaky web interface, and anyone
using that interface may well not see replies (so of course he may not have
seen my reply either).
--
David Biddulph

I sometimes feel that my posts are not visible to some posters, David.

Pete
 
W

William

It is also possible to force one or more zeros to display without changing
the cell format to text, and sometimes this is desirable. Select the cells,
go to Format/Cells/CUSTOM and in the "Type" box enter the same number of
zeros for which you need digits in your cells. If you enter 000000 and in a
cell type "1234" it will show as 001234.

William
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top