How do I add a 1 to all phone #s(cells) in a column?

[pookie954]

I have a 5000+ list of phone #s in an excel sheet. I need to add a 1 to each
of them. The numbers look like this: 9545555555. I wabt to add the 1
prefix without a dash or space. Could someone be so kind as to help this
newbie???

 

[Cimjet]

Hi Pookie954
="1"&A1 This formula will add 1 to the numbers, just type in with the right
reference eg:"A!" and copy down.
Regards
Cimjet

 

[pookie954]

Thanks Cimjet. Could you elaborate on "copy down?" Thx

 

[Bob Phillips]

After entering the formula in the first cell, move the cursor towards the
bottom right of the cell until the cursor changes to a thick black cross. At
that point click the left mouse button, and drag the formula down.

BTW, I would use a formula of

=10^LEN(A1)+A1

to preserve it as numeric.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[jackgopher]

QUOTE: "Thanks Cimjet. Could you elaborate on "copy down?"


After writing the formula, select the cell containing it.
You will see a small square in the bottom right corner of that selected
cell. Put the mouse cursor over it, and drag it downwards.



_________________
'Excel 2007 tutorial' (http://www.microsoft-office-excel.com)

 

[MartinW]

Hi Bob,

I'm confused on two counts.

1 Why would you need to preserve phone numbers as numeric?

2 If for whatever reason you do want to preserve them as numeric
why use a function call instead of just =("1"&A1)*1 ?

Not trying to be a smarty, just intrigued.
Martin

 

[Cimjet]

Thank you both ( Bob & Martin ) for the information. I never realized I was
changing the format.
I learn something everyday on these newsgroups and loving it.
Maybe one day will be has good as you guys.
Regards
Cimjet

 

[pookie954]

Thank you. This worked perfectly! Problem solved.

 

[pookie954]

Thanks a bunch. It worked!

After entering the formula in the first cell, move the cursor towards the
bottom right of the cell until the cursor changes to a thick black cross. At
that point click the left mouse button, and drag the formula down.

BTW, I would use a formula of

=10^LEN(A1)+A1

to preserve it as numeric.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)