How can I automatically add an area code to a number in a cell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to change telephone numbers from format (XXX) XXX-XXXX to
1-XXX-XXX-XXXX. I also want to add 1-XXX to XXX-XXXX in cells.
 
Provided that you only have those two formats, you may try a formula like this:
=IF(LEN(A1)>10,"1-" & MID(A1,2,3) & "-" & RIGHT(A1,8),"1-XXX-" & A1)
If the 1-XXX is not the same in all cases, you may need to edit the formula
to point a reference instead of the hard-coded number.

Hope this helps,
Miguel.
 
Thanks. I'll give that a try today.

Miguel Zapico said:
Provided that you only have those two formats, you may try a formula like this:
=IF(LEN(A1)>10,"1-" & MID(A1,2,3) & "-" & RIGHT(A1,8),"1-XXX-" & A1)
If the 1-XXX is not the same in all cases, you may need to edit the formula
to point a reference instead of the hard-coded number.

Hope this helps,
Miguel.
Click to expand...
 
The formula works nicely for instering "1-585" into the cells.

My problem is (and I didn't explain it well enough the first time) that I
have existing data in the cells. For example: telephone numbers exist in
cells AE2....AE2500. They already have phone numbers in them like 555-1212
and (716) 555-1212. I want to systematically change 555-1212 to
1-585-555-1212 and (716) 555-1212 to 1-716-555-1212.

I am an intermediate user of Excel and can mess with formulas a bit, but it
seems that whatever formula I use, I will still have to re-enter the data
into each cell (all 2500 of them). That's what I'm trying to avoid if I can.

Thanks for your help.
 
In a blank column (let's assume this is column AM), enter this into
cell AM2:

=IF(LEFT(AE2,1)="(","1-"&MID(AE2,2,3)&"-"&RIGHT(AE2,8),"1-585-"&AE2)

and copy down to AM2500. This assumes the phone numbers are in the two
formats described, and works by examining the first character of the
number - if it is "(", as in "(716) 555-1212", this will be converted
to "1-716-555-1212", otherwise it will have "1-585-" appended to the
beginning of it.

If you want these converted numbers to replace the ones you have, then
highlight the cells AM2:AM2500, click <copy>, then Edit | Paste Special
| Values (check) | OK then <Enter> - this will have fixed the values in
column AM. You could then <cut> these values and <paste> them to
overwrite the values in column AE.

Hope this helps.

Pete
 
Disaster-

Could you start by a search and replace?

Search "(", replace ""
Search ") ", replace "-"

Then dealing with adding area codes and ones might be simpler...

Beege
 
Pete...

It worked beautifully. You've made a few people here quite happy today.

Thanks.
 
Pete - your formula would work perfect for us with one exception. We want to
convert telephone numbers xxx-xxx-xxxx to (xxx) xxx-xxxx and to add area
codes. I do not understand complicated formula's so how would I change this
formula to work for me? I will be eternally grateful
CodyKid
 
Back
Top