Changing the format of a phone number

Spreadsheet A has the phone number in one cell formatted like:

(123) 456-7890

Spreadsheet B has the phone number separated in three separate columns like:

123 456 7890

I used the CONCANTENATE function to string the text together, but if the
phone number has a 0 starting the group it isn't showing up in my new cell.
What am I doing wrong? See below:
Phone
(360) 929-2001 360 929 2001
(425) 971-9043 425 971 9043
(360) 929-2001 360 929 2001
(253) 720-5735 253 720 5735
(111) 222-1234 111 222 1234
(541) 410-45 541 410 0045
(208) 438-5296 208 438 5296
(541) 689-143 541 689 0143
(360) 253-2131 360 253 2131
(360) 253-2131 360 253 2131
(480) 981-3941 480 981 3941
(480) 981-3941 480 981 3941
(217) 257-8406 217 257 8406
(602) 997-6349 602 997 6349
(602) 997-6349 602 997 6349
(602) 997-6349 602 997 6349
(111) 222-1234 111 222 1234
(520) 297-8855 520 297 8855
(303) 741-4568 303 741 4568
(801) 566-8844 801 566 8844
(801) 566-8844 801 566 8844
(720) 837-929 720 837 0929

=TEXT(A2,"000")&" "&TEXT(B2,"000")&"-"&TEXT(C2,"0000")


replace the cell reference with the real one you are using

No need to use the CONCATENATE function if that's what you are doing, the
ampersand works the same way



--


Regards,


Peo Sjoblom



"scr" wrote in message
...
Spreadsheet A has the phone number in one cell formatted like:

(123) 456-7890

Spreadsheet B has the phone number separated in three separate columns
like:

123 456 7890

I used the CONCANTENATE function to string the text together, but if the
phone number has a 0 starting the group it isn't showing up in my new
cell.
What am I doing wrong? See below:
Phone
(360) 929-2001 360 929 2001
(425) 971-9043 425 971 9043
(360) 929-2001 360 929 2001
(253) 720-5735 253 720 5735
(111) 222-1234 111 222 1234
(541) 410-45 541 410 0045
(208) 438-5296 208 438 5296
(541) 689-143 541 689 0143
(360) 253-2131 360 253 2131
(360) 253-2131 360 253 2131
(480) 981-3941 480 981 3941
(480) 981-3941 480 981 3941
(217) 257-8406 217 257 8406
(602) 997-6349 602 997 6349
(602) 997-6349 602 997 6349
(602) 997-6349 602 997 6349
(111) 222-1234 111 222 1234
(520) 297-8855 520 297 8855
(303) 741-4568 303 741 4568
(801) 566-8844 801 566 8844
(801) 566-8844 801 566 8844
(720) 837-929 720 837 0929

Thank you!

"scr" wrote:

Spreadsheet A has the phone number in one cell formatted like:

(123) 456-7890

Spreadsheet B has the phone number separated in three separate columns like:

123 456 7890

I used the CONCANTENATE function to string the text together, but if the
phone number has a 0 starting the group it isn't showing up in my new cell.
What am I doing wrong? See below:
Phone
(360) 929-2001 360 929 2001
(425) 971-9043 425 971 9043
(360) 929-2001 360 929 2001
(253) 720-5735 253 720 5735
(111) 222-1234 111 222 1234
(541) 410-45 541 410 0045
(208) 438-5296 208 438 5296
(541) 689-143 541 689 0143
(360) 253-2131 360 253 2131
(360) 253-2131 360 253 2131
(480) 981-3941 480 981 3941
(480) 981-3941 480 981 3941
(217) 257-8406 217 257 8406
(602) 997-6349 602 997 6349
(602) 997-6349 602 997 6349
(602) 997-6349 602 997 6349
(111) 222-1234 111 222 1234
(520) 297-8855 520 297 8855
(303) 741-4568 303 741 4568
(801) 566-8844 801 566 8844
(801) 566-8844 801 566 8844
(720) 837-929 720 837 0929