Format Cell/Number

[peyman]

Hi,
I've a column of "purchase order" numbers starting with zero like
025639,045698,083354,... .I wonder somehow the Format Cell/Number of the
cells in this column is set to "Special"; "Locale(Location): Chinese (PRC) ;
"Type" : some chinese words; and "sample" : 085815.
When you are on the cell ,the content on the toolbal (infront of fx )is
84325 , but what you see on the spreadsheet is 084325.
And also when I change the format cell to "text", the 0's at the beginning
of the contents are removed.I wanna know if there is any way to change the
format cell to "text" or something else and at the same time keep the 0's in
the cells?
I hope my explanation is clear!!
Thanks for ur help.
Peiman

 

[Pete_UK]

Assume these numbers are in column A. Insert a new column B if
necessary (temporarily) and put this formula in B1:

=TEXT(A1,"000000")

Then copy this down by double-clicking the fill handle (the small
black square in the botton right corner of the cursor). With the cells
in column B highlighted, click <copy>, then Edit | Paste Special |
Values (check) | OK then <Esc>. You can now delete column A, but if
you have formulae elsewhere which reference A, then copy B into A to
over-write the values, then delete column B to return your sheet to
how it was before.

Hope this helps.

Pete

 

[CKK]

More simply as to set the number format as 6 digits (000000) display when you
needed 0 shown in prefix of any digits typed in less than 6 digits, such as
you type in 5 digits in a cell, like 12345 shown in fx field (edit bar) then
012345 displayed on the cell.

To set this, 1st, select the whole COLUMN/ROW (s) or the area (like A1:C20)
you want for this format, then click (from the menu or the right button of
the Mouse)FORMAT-->CELL-->CUSTOM, type in 0...(at the Style field) as many as
how you want a integrated digits display in a cell, for 6 digits shown as
above then type in six 0 as 000000 or for 8 digits then 00000000

hope it's helpfull!

 

[peyman]

That's great Gord! but what type of COPY is that?! I mean doubleclicking on
the tiny black square.Any difference from regular copying like dragging down
the first cell's formula?
The format cell I have now is GENERAL not TEXT so if I need to re-enter a
new purchase order no. like 012345 in the column , the 0 will be removed.Any
solution?
Thanx

 

[peyman]

Sorry, It didn't work.
I changed the Format Cells/Number to Custom with 6 zeros but still the
appearnce is like 012345 in the cell and 12345 in the fx Edit Bar.
Thanx anyway.

 

[David Biddulph]

If you want the "number" to be treated as text (and thus keep its leading
zeroes), you must format the cell as text *before* you enter the "number",
or prefix the "number" with an apostrophe.

 

[peyman]

Thanx David, I knew it but the column's format is somehow changed!!

 

[Gord Dibben]

You are replying to Pete_UK's post but...................

Double-clicking is same as dragging but much faster if adjacent column has
data.

Pete's TEXT formula will preserve the leading zeros.

To enter a new number with a leading 0 in any cell preface the number with
an apostrophe before entering.

i.e. '012345

The apsostrophe won't show.


Gord Dibben MS Excel MVP

 

[peyman]

Well, the procedure Pete posted me gives me a column with "GENERAL" format
cell at the end but,I just wanna know if there is any way to have it in
"TEXT" format? because I want to set the whole column's cells format to
"text" and enter the PO numbers with leading zeros without needing to enter
apsostrophe!!
Is it possible?
Thanx

 

[Gord Dibben]

The formula Pete gave you makes text numbers out of regular numbers.

So I don't know how they got to General Format

But instead of prefacing with an apostrophe just fort the new cells as text
and enter 012345


Gord

 

[CKK]

Sorry, I can't get what you want if it did't...

BTW, seem that the original format was set by others which referred to a
certain template (file) you may not have it.