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

I need a way to actually have these leading zeroes become part of the
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 into
Notepad, format the column I want to paste into to text, and then paste
the data from Notepad back into Excel. I figure there’s GOT to be a way
to skip this step using Notepad, but I’ve no idea what it is. When I’m
dealing with thousands of UPCs, the Notepad solution is going to be
really cumbersome. Can anybody help me?


--
Jason Grunert
------------------------------------------------------------------------
Jason Grunert's Profile: http://www.excelforum.com/member.php...o&userid=37115
View this thread: http://www.excelforum.com/showthread...hreadid=568465

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


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568465

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


--
Jason Grunert
------------------------------------------------------------------------
Jason Grunert's Profile: http://www.excelforum.com/member.php...o&userid=37115
View this thread: http://www.excelforum.com/showthread...hreadid=568465

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


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568465

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

"mr_ben" wrote:


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


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568465

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


--
Jason Grunert
------------------------------------------------------------------------
Jason Grunert's Profile: http://www.excelforum.com/member.php...o&userid=37115
View this thread: http://www.excelforum.com/showthread...hreadid=568465

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 wrote:
Hello, I have a spreadsheet with a column of UPC numbers, some of which
need to have leading zeroes, so I set up a Custom number format with 11
zeroes (00000000000) so that they all display with the correct number of
digits. However, I eventually need to convert these columns to text for
a data import I’m doing, and when I do so, the leading zeroes
disappear.

I need a way to actually have these leading zeroes become part of the
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 into
Notepad, format the column I want to paste into to text, and then paste
the data from Notepad back into Excel. I figure there’s GOT to be a way
to skip this step using Notepad, but I’ve no idea what it is. When I’m
dealing with thousands of UPCs, the Notepad solution is going to be
really cumbersome. Can anybody help me?

Thanks, Len! That seems to do the trick!

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


--
Jason Grunert
------------------------------------------------------------------------
Jason Grunert's Profile: http://www.excelforum.com/member.php...o&userid=37115
View this thread: http://www.excelforum.com/showthread...hreadid=568465

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.



"Jason Grunert" wrote:


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

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


--
Jason Grunert
------------------------------------------------------------------------
Jason Grunert's Profile: http://www.excelforum.com/member.php...o&userid=37115
View this thread: http://www.excelforum.com/showthread...hreadid=568465