Currently I have a large spread sheet 38,000 lines that I need to add a -
between the phone number so the file can be uploaded to another system. I'm
working on excel 2003 and was wondering is there a formula that would allow
me to do this?

The current data looks like this 3145555555. I need it to look like this
314-555-5555

Any ideas?

format>cells>special>telephone number
or
format>cells>custom>###-###-####

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Lee" <(E-Mail Removed)> wrote in message
news:03CB5F9B-E089-48EC-AF23-(E-Mail Removed)...
> Currently I have a large spread sheet 38,000 lines that I need to add a -
> between the phone number so the file can be uploaded to another system.
> I'm
> working on excel 2003 and was wondering is there a formula that would
> allow
> me to do this?
>
> The current data looks like this 3145555555. I need it to look like this
> 314-555-5555
>
> Any ideas?

If you want to change the data in addition to the format you might consider
this formula then copy the value to another cell.

=CONCATENATE(LEFT(A3,3),"-",MID(A3,4,3),"-",RIGHT(A3,4))

"Lee" wrote:

> Currently I have a large spread sheet 38,000 lines that I need to add a -
> between the phone number so the file can be uploaded to another system. I'm
> working on excel 2003 and was wondering is there a formula that would allow
> me to do this?
>
> The current data looks like this 3145555555. I need it to look like this
> 314-555-5555
>
> Any ideas?

Hi,
If your phone number data starts in F2, put this in G2 and copy down.
=LEFT(F2,3)&"-"&MID(F2,4,3)&"-"&RIGHT(F2,4)
Then copy and paste the hyphenated data over the original data, and delete
the column G data.

Or, select the phone number column, and custom format with:
###-###-####

Regards - Dave.

On Mon, 23 Jun 2008 09:36:04 -0700, Lee <(E-Mail Removed)> wrote:

>Currently I have a large spread sheet 38,000 lines that I need to add a -
>between the phone number so the file can be uploaded to another system. I'm
>working on excel 2003 and was wondering is there a formula that would allow
>me to do this?
>
>The current data looks like this 3145555555. I need it to look like this
>314-555-5555
>
>Any ideas?

This will work with phone numbers with or without area codes prepended:

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

--ron