Leading zeroes get dropped when converted to text

J

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
 
M

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
 
J

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
 
B

Battagfv

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

Jason Grunert

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

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
 
J

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!
 
S

Sloth

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top