phone number dilemma

A

alexm999

Im putting together a list and I need some assistance.
In cell A1 I have a phone number. 2125551212 for example.
I need it to look like 12125551212 - it needs to have a one in front on
it.
I have an excel list of over 20000 numbers. Is there a way to easily
put a 1 in front of all the numbers?
 
C

comotoman

insert a column after "a" in new column "b'' type in '=1&a1'

then fill down entire list.
 
K

Ken Wright

In B1 put

=1&A1

and then copy down. Lastly copy Col B, and paste special as values and then
delete Col A

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
C

comotoman

or insert macro

Sub AddOne()

ActiveCell.FormulaR1C1 = "=1&RC[-1]"
Range("B1:B20000").Select
Selection.FillDown
End Sub
 
E

Elkar

Assuming all your phone numbers are this format, then yes there is an easy
way to do this.

1. In any empty cell, enter the value: 10000000000
2. Copy that cell
3. Highlight your list of phone numbers
4. Select "Paste Special..." from the Edit Menu
5. Click the box labeled "Add" in the Operation section
6. Click "OK"
7. Delete the 10000000000 from the cell that you entered earlier.=

That should do it.
 
M

Michael

Hi Alex. In B1 type "=1&A1. This will put a 1 in front of the number in A1.
Then just copy down for your 20,000 entries. When finished copy Column B on
itself, using Edit - Paste Special - Values. HTH
 
J

Jay

Im putting together a list and I need some assistance.
In cell A1 I have a phone number. 2125551212 for example.
I need it to look like 12125551212 - it needs to have a one in front on
it.
I have an excel list of over 20000 numbers. Is there a way to easily
put a 1 in front of all the numbers?
Click to expand...

One way is to select the column and use:
Format > Cells > Number
Then for "Category" select "Custom" and put this for the "Type":
1##########
 
Top