Adding a "1" to Phone Number field

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hello,

I have about 4000 phone numbers that I need to add the "1"
in front of number for long distance. Is there way to do
this easily? Any help is appreciated.

Thanks

Mark
 
Create a helper column to the right of your phone number data. Assuming
your data begins in A1 and your desired format is 1-number, then in B1, use:

="1-"&A1

and copy down

--
Michael J. Malinsky
Pittsburgh, PA

"I was gratified to be able to answer promptly,
and I did. I said I didn't know." -- Mark Twain
 
Hi

If the Phone number is a "text" field then complete a
formula to create the new phone number.

Eg. Cell A1 => 3565-5558
In Cell A2 type ="1"&A1
Result 13565-5558

If the phone number is a "number" ie. 35655558, then you
will need to convert the number to a "text", use the
following formula -

Eg.
="1"&TEXT(A1,"####")

I hope this helps.
 
Mark

Assuming the phone numbers are in column A.

I B1 enter ="1 " & A1

Double-click on B1 fill handle to copy down 4000 rows.

When happy, copy column B and Paste Special(in place)>Values.

Delete column A.

Gord Dibben Excel MVP
 
assuming all your phone numbers are in column A, begining cell A1, thi
formula typed ub cell B1 and copied down will return what you want.

Or, select the range containing the phone numbers, and run this code:

Sub add_ones()
For Each cc In Selection.Cells
cc.Value = "1" & cc.Value
Next
End Su
 
Back
Top