Linefeed, phone number formating

Hello, I would appreciate some help here. I'm using excel 2007, I'm having trouble with two problems. 1. The phone number format that I have is: example: (334)787-7121, the format that I need is shown below. 2. I'm not sure of the linefeed command using excel 2007.


1. Flat Text File – Full List
The Full List in a Flat Text File has one three-digit area code, a comma, and a seven-digit telephone number per line, with a linefeed at the end of each line:
123,4567890
123,4567890
123,4567890

Any help would be greatly appreciated.

Cutter, thank you for the reply this is what I'm getting back with the Substitute funtion:

Original: (334)363-0970

using =SUBSTITUTE(MID(A1,1,6),")",",")&RIGHT(A1,4)&CHAR(10)

it returns 3343630970, I need it to return: 334,3630970 where in the formula do you place (,) a comma behind the area code?

Have a closer look at the values in the MID() function.

I used: MID(A2,2,7)
You used: MID(A1,1,6)

change the 1,6 to 2,7 and you'll get the right result

The SUBSTITUTE() function is replacing the ")" with a comma

Cutter, when I use:
=SUBSTITUTE(MID(A2,2,7),")",",")&RIGHT(A2,4)&CHAR(10)
on Phone number: (334) 363 0914, the return is 34363090914

what I'm looking for is 334,3630914 linefeed.


Opelika 36801 (334) 363-0914 "34363090914" 45-49 F Camarata John 704 704 Avenue D Avenue D C003, 599 Residents

Special-K

The current Phone # format is (334)787-6060
I need the format to read: 334,7876060
There are over 1000 numbers and the instruction further say include linefeed comand.

Any help that you can give I would appreciate it.

Are you saying that the underlying value is 3347876060 but that you have formatted that cell to display as (334)787-6060 ?

In that case you just need to apply a new Custom Format to the cell(s) using this format string:

000","0000000

Hope this helps.

Pete

The formula I gave you works on phone numbers formatted as you say they are - (334)787-6060.
The return from that format (using what I gave you) would be: 334,7876060

It does NOT work on phone numbers formatted like this - (334) 363 0914
For that format the formula I gave you returns this: 334, 360914
so I don't see how you're getting: 34363090914

Are you saying some of your phone numbers have different formats, like spaces in them?

I think you're right, Pete.

In that case the OP would need: =TEXT(A2,"000"",""0000000")&CHAR(10)

Pete the cell currently is : 3347876060

I need the cell to show: 334,7876060