Excel 2002 drops a leading 0 in a number

[potco2]

I'm trying to enter a number in an excel cell that begins with the number 0 (ie: 034567).  when i move to the next cell excel drops the 0 and just displays the rest of the numbers.  i need for the 0 to be displayed.

How do i get the 0 to display?

potco2

[dl65]

potco2.....  Open Excel , click format/style ...... click modify..... then select text ....... then click ok ...... ( that shouild do it , I just tested it here .)

dl65  

[potco2]

i tried this and it does allow the leading 0 to remain in place.  but if i have a column of numbers some with a leading 0 and some without and want to do a sort it does not sort correctly.  if it sorted correctly all the numbers with a leading 0 should be at the top of the column.

do you have anyother ideas?

[soybean]

I wonder why you put this question in the Hardware section, not in Software.  Anyway, you must be doing something wrong.  I tested sorting of some numbers, formated as Text, and the numbers starting with 0 do sort together at the beginning of the list when sorting in ascending order.

Do have the entire column, or at least the rows to be sorted, all formatted as text?  You need to do that to get this to work right.  When you start the sort, you should get a Sort Warning panel; in it, check the box for "Sort numbers and numbers stored as text separately".

[potco2]

i thought i had put this under software, but evidently i didn't.  sorry this is my first time in here.

you are right it does work as long as all the cells have the same format.  that was my problem i only had those that needed the leading 0 set to text format.  when i changed all cells to text it worked fine.

thank you for you help.

potco2

[dl65]

Topic closed and locked


dl65  

[Rob Pomeroy]

...and opened again so I can mention this: to force any data to be interpreted as text, just put an apostrophe at the start.  E.g. '01234

The apostrophe is not considered part of the contents of the cell, merely an indication of how it is to be interpreted.