Excel and Leading Zeros

[ladyck3]

We have some stock we are scanning into Excel, using a bar code reader. The numbers have leading zeros but of course by default, Excel lops them off. Is there a way to format (formatting as text does not do it, but manually entering a ' will work) to configure the cells so when the item is scanned, Excel will retain the leading zeros?

Your help would be GREATLY appreciated.

Ladyck3

LadyCK3
aka: Laurie

 

[AnotherHiggins]

I would expect formatting the column as TEXT would do it for you. But since it doesn't....

Is the stock number always the same length (number of digits)? If so, you could use a 'helper column' with a formula like this:
[tab][COLOR=blue white]=text(A1,"00000")[/color]

The above would convert all numbers to 5 characters with leading zeros before any number with less digits. However many characters the stock number contains, just include that many zeros in the formula.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[dneufarth]

format, cells, number, custom, type 00000 (for 5 digit numbers)

Dave

 

[AnotherHiggins]

format, cells, number, custom, type 00000 (for 5 digit numbers)

[attn]WARNING[/attn]:
Whether this will work depends on what you need to do with the numbers.

Changing the format as suggested will not retain leading zeros. That only forces the cell to display the leading zeros, but the number (not string) contained in the cell remains unchanged.

Example:
format a cell as described in the previous post. Type in 123. You will see 00123 in the cell, but the number in the cell is still just 123 as you can see in the Formula bar (or by double clicking in the cell).

If you are using these to track inventory, I suggest converting them to numeric strings (different than a number) so the leading zeros are actually stored as part of the data.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.