Leading zeroes get dropped when converted to text

[Jason Grunert]

Hello, I have a spreadsheet with a column of UPC numbers, some of whic
need to have leading zeroes, so I set up a Custom number format with 1
zeroes (00000000000) so that they all display with the correct number o
digits. However, I eventually need to convert these columns to text fo
a data import I’m doing, and when I do so, the leading zeroe
disappear.

I need a way to actually have these leading zeroes become part of th
value in the formula bar so I don’t lose them when I convert to text
The only way I’ve been able to do it so far is to paste the values int
Notepad, format the column I want to paste into to text, and then past
the data from Notepad back into Excel. I figure there’s GOT to be a wa
to skip this step using Notepad, but I’ve no idea what it is. When I’
dealing with thousands of UPCs, the Notepad solution is going to b
really cumbersome. Can anybody help me

 

[mr_ben]

go to format cells and go to custom...

in the box type....

00000000000# (11 zeros)

job done

that way any number less than 11 numbers will have the relative leading
zeros


hth

 

[Jason Grunert]

Yeah, I've done that, but when I later try to convert that column t
text, the leading zeroes disappear. Thanks for the response though

 

[mr_ben]

oh you didn't make that very clear.....hmmmm will have another sta
now..

 

[Guest]

If it's going to be a text column anyway,
why not replace all the leading number 0's with the letter O?

 

[Jason Grunert]

I wish I could do that, but the import wouldn't work correctly if there
were letters there instead of numbers.

 

[LenB]

Hi Jason. This might work for you. If your 11 digit number is in B2,
this formula will make an 11 character text value with leading zeros:

=LEFT("00000000000",11-LEN(B2)) & B2

Len

 

[Jason Grunert]

Thanks, Len! That seems to do the trick!

(Though if anybody else has another solution involving formattin
instead of a formula, I'd be happy to hear them!

 

[Guest]

If you plan on converting numbers to text, you should either enter them with
a leading apostrophe, or format the cell to text before entering the numbers
in.

If you want to change an entire column of numbers to text with leading zeros
use this procedure.
1. In a helper column enter this formula and copy it down the entire
selection
=TEXT(A1,"00000000000")
2. Copy and paste special. Select "values"
3. Format the column as text.
4. Delete the helper column.