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

Hi

Select the column, then goto Format Cells Number Custum Type:
#######0000000 OK

Hopes this helps.

---
Per

On 8 Maj, 16:22, 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

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

Wow! That's amazing! I will print and keep this one. Do you know how many
times I've wanted to do something similar!! I just don't know all these
little tricks!!
Thank you so much. Connie

"Per Jessen" wrote:

Hi

Select the column, then goto Format Cells Number Custum Type:
#######0000000 OK

Hopes this helps.

---
Per

On 8 Maj, 16:22, 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

This works, too! Thank you! I think the solution given by Per Jessen is
quicker, although I DID ask for a formula....only because I thought that was
the only way to go. Thank you so much. I'm printing this one, too, because
this one may suit better in another spreadsheet. 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

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