Leading Zero

[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 !!!

 

[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

 

[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

 

[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.

 

[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

 

[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.

 

[Rumy]

You are awesome.... Thank you sir Have a Gud Night !!!!

 

[David Biddulph]

See Pete_UK's reply.
--
David Biddulph

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

 

[Pete_UK]

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

Pete

 

[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

 

[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

 

[Pete_UK]

Yes, but that wasn't what the OP was asking for.

Pete