Domenic, I actually ended up using yours because Per Jessen's forces every
number entered thereafter to a 7-digit number. This is okay for some things,
but some numbers don't start with zero, so if the number is typed incorrectly
with just six digits, then his method will cause a zero to be added to the
front of the number. Whereas with yours I was able to change the whole
column to correct numbers and then I used Data Validation to restrict the
column to a text length of seven. This will pick up when someone has
forgotten to put the zero in front of the numbers that have six digits or
will simply alert them to the fact that their number is incorrect. Thanks
again. Both solutions works for different purposes! Connie

"Domenic" wrote:

In article ,
Connie Martin wrote:

I have a column of numbers, over 800 rows. Some numbers are 7 digits and
some are 6 digits. I need a zero at the beginning of all the numbers with 6
digits. I have formatted the column to text so that I can do this because
it's not important that this column be recognized in number format. However,
is there a formula I could use to drag down the entire column that would pick
out all the numbers with 6 digits and throw a zero at the beginning of the
number? I'd hate to have to do this manually! Thank you. Connie


Assuming that A2:A800 contains the data, try...

B2, copied down:

=TEXT(A2,"0000000")

Then, to delete Column A, try the following...

1) Select Column B

2) Edit Copy

3) Edit Paste Special Values Ok

4) Format Column B as 'Text'

5) Delete Column A


--
Domenic
http://www.xl-central.com