Leading zeroes get dropped when converted to text

  • Thread starter Thread starter Jason Grunert
  • Start date Start date
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
 
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
 
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
 
If it's going to be a text column anyway,
why not replace all the leading number 0's with the letter O?
 
I wish I could do that, but the import wouldn't work correctly if there
were letters there instead of numbers.
 
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
 
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!
 
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.
 
Back
Top