add data into a cell

P

Paul

can i add () to the area code in all my cells that contan a phone number all
at one time. is there a formula that can be created to accomplish this task?

Paul
 
B

Bernard Liengme

No, but read on:

Suppose all the phone numbers are in G1:G20
1 If column H is not empty insert a new column by right clicking the H
header (we will get rid of it soon)
2 In H1 enter the formula ="(902)-"&G1 ; of course, you will want your area
code, and the parentheses are optional
3) Copy down the column
4) Select all entries in H and use COPY. With the data still selected use
Edit | Paste Special and specify VALUES
Now the entries in H are what you want
5) Click the G header and use Delete to remove

hope this helps

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
 
R

Ron Rosenfeld

can i add () to the area code in all my cells that contan a phone number all
at one time. is there a formula that can be created to accomplish this task?

Paul
Click to expand...

If your numbers are entered with just digits, then just format the values:
Format/Cells/Number/Special Phone Number.

If your values are already text strings, with a variety of possible separators,
you could first extract just the numbers, and then put that result into a
formula:

=TEXT(extracted_numbers,"[<=9999999]###-####;(###) ###-####")

How best to extract the numbers depends on how they have been entered.
--ron
 
Top