Adding a zero to the front of numbers

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

Connie Martin

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.
 
Connie Martin said:
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
Click to expand...


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
 
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
 
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, 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
 
Back
Top