Thread: [SOLVED] How can I automatically add an area code to a number in a cell?

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.

"Disaster Lady" wrote:

> 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.

Thanks. I'll give that a try today.

"Miguel Zapico" wrote:

> 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.
>
> "Disaster Lady" wrote:
>
> > 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.

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.

"Miguel Zapico" wrote:

> 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.
>
> "Disaster Lady" wrote:
>
> > 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.

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

"Disaster Lady" <DisasterLady@discussions.microsoft.com> wrote in message
news:53F07FCA-5771-42AE-92EA-CADE78476B3C@microsoft.com...
> 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.
>
> "Miguel Zapico" wrote:
>
>> 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.
>>
>> "Disaster Lady" wrote:
>>
>> > 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.

Pete...

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

Thanks.

"Pete_UK" wrote:

> 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
>
>

Thanks for the feedback.

Pete

Thanks for the feedback.

Pete