Zeros at the first of the number

  • Thread starter Thread starter Susie
  • Start date Start date
S

Susie

I use UPC #'s a lot. Most are preceeded by a '0'. When I enter the number,
the '0' does not appear and it will not upload to another program. How can I
format the cell or column to accept 12 digits, including the preceeding '0'?
 
You have 2 choices:
1) Set the Number Format of the column to TEXT,
<format><cells><number tab>.....Category: Text

Then enter the numbers.

2) Type an apostrophe (') before you enter each number
Example: '00012345678

Note: The apostrophe will not be visible in the cell,
only in the formula bar.


Either way, Excel will not remove the leading zeros.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Ron,

I tested both and they work, but they only seem to work AFTER the format is
set of as you enter the '. My problem, though, is that all these UPC's are
already in my spreadsheet. I tried both of your ideas on them and they
didn't help previously entered data.
 
My problem, though, is that all these UPC's are
already in my spreadsheet<<

I see.....That's a different problem.

Since the leading zeros are already gone.
try this:

1) Set the number format of the cells to TEXT
2) Create a helper column off to the right with this kind of formula:
=TEXT(A2,"000000000000")
Copy that formula down as far as you need

3) Select the range of helper column formulas
<edit><copy>
4) Select the source range
<edit><paste special>....Check: Values......Click: [OK]

Then just erase the formulas in the helper column

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Assuming your UPC numbers which should be 10 digits are in A1:A100 you could
enter this in B1: =REPT("0",10-LEN(A1))&A1 and drag it down through B100.
And then replace your column A UPC's with the column B UPC's

Tyro
 
I should have said to do a copy/paste special/values of the UPC's in column
B to replace the UPC's in column A.

Tyro
 
Ron,

You got it !!! Thanks so much for your help.



Ron Coderre said:
already in my spreadsheet<<

I see.....That's a different problem.

Since the leading zeros are already gone.
try this:

1) Set the number format of the cells to TEXT
2) Create a helper column off to the right with this kind of formula:
=TEXT(A2,"000000000000")
Copy that formula down as far as you need

3) Select the range of helper column formulas
<edit><copy>
4) Select the source range
<edit><paste special>....Check: Values......Click: [OK]

Then just erase the formulas in the helper column

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Susie said:
Ron,

I tested both and they work, but they only seem to work AFTER the format
is
set of as you enter the '. My problem, though, is that all these UPC's
are
already in my spreadsheet. I tried both of your ideas on them and they
didn't help previously entered data.
Click to expand...
Click to expand...
 
You're very welcome, Susie.......I'm glad I could help.
(and thanks for the feedback)

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

Susie said:
Ron,

You got it !!! Thanks so much for your help.



Ron Coderre said:
My problem, though, is that all these UPC's are
Click to expand...
already in my spreadsheet<<

I see.....That's a different problem.

Since the leading zeros are already gone.
try this:

1) Set the number format of the cells to TEXT
2) Create a helper column off to the right with this kind of formula:
=TEXT(A2,"000000000000")
Copy that formula down as far as you need

3) Select the range of helper column formulas
<edit><copy>
4) Select the source range
<edit><paste special>....Check: Values......Click: [OK]

Then just erase the formulas in the helper column

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


Susie said:
Ron,

I tested both and they work, but they only seem to work AFTER the
format
is
set of as you enter the '. My problem, though, is that all these UPC's
are
already in my spreadsheet. I tried both of your ideas on them and they
didn't help previously entered data.

:

You have 2 choices:
1) Set the Number Format of the column to TEXT,
<format><cells><number tab>.....Category: Text

Then enter the numbers.

2) Type an apostrophe (') before you enter each number
Example: '00012345678

Note: The apostrophe will not be visible in the cell,
only in the formula bar.


Either way, Excel will not remove the leading zeros.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I use UPC #'s a lot. Most are preceeded by a '0'. When I enter the
number,
the '0' does not appear and it will not upload to another program.
How
can I
format the cell or column to accept 12 digits, including the
preceeding
'0'?
Click to expand...
Click to expand...
Click to expand...
 
Highlight the cells you want to format to display 12 digits including leading
zeros.
then select Format>Cells>Custom...........in the "Type" field, type in 12
zeros "000000000000" (without the quote marks)
Now every cell formatted this way will show 12 digits in the cell and will
include leading zeros wether you type them in or not. eg if you entered 12
or 012 or 00012 or 000000012 then the cell will display 000000000012

Hope this helps
 
Back
Top