Leading zeroes - cell shows it, value does not

[obiwaynekenobi]

Okay here is the problem. I am working with UPC codes, many of which have a leading zero. Excel is formatting them as numbers, and while I can play with the formatting to make the zero appear, in the formula bar the value is still shown without the zero. Since this is a UPC code it shouldn't even be treated as a number, but as text; however changing the format to text immediately removes the leading zero. The same thing happens if I use "Text to Columns" - it removes the leading zero because the actual value has removed it, just it's being formatted to display one. I need the value to have the leading zero, not the cell.

There are some 25,000 items here. Is there *any* way to change the *value* to text AND keep the leading zero? This data is to be imported into SQL Server so it needs to be text, but just changing the format still removes the leading zero, which will make the data invalid.

[oldchippy]

Hi,

Does this work for you with your numbers in column A and this in B1 copied down?

="0"&A1

[shg]

You can handle them as text throughout. Even when doing text to columns, you have the option of maintaining text format in the last dialog.

[Happy_alexx]

type ' before the codes you are entering - it should keep leading zeros.