Change the concatenate formula to put the format

=TEXT(A1&B1,"format")

--

HTH

RP
(remove nothere from the email address if mailing direct)


" wrote in
message ...
Hi,


I'm trying to format a column of cells that were created by combining 2
different cells using concatenate command.


The result is a phone number, but when I go to format it (so it
displays as a phone number (###) ###-####), nothing happens.


I'm using Excel 97 (don't laugh), but this is the case on XP as well


Thanks




--
Meg Darnell, LMT, CD
Director of Alumni Services
Swedish Institute College of Health Sciences
212-924-5900 x142

This is a common problem:

if 1234569876 is a number then Format Cells... Special Phone Number
will work. It won't work if 1234569876 is a text string.

If 1234569876 is a text string in say A1 then use =value(A1) and format it.
--
Gary''s Student


" wrote:

Hi,


I'm trying to format a column of cells that were created by combining 2
different cells using concatenate command.


The result is a phone number, but when I go to format it (so it
displays as a phone number (###) ###-####), nothing happens.


I'm using Excel 97 (don't laugh), but this is the case on XP as well


Thanks




--
Meg Darnell, LMT, CD
Director of Alumni Services
Swedish Institute College of Health Sciences
212-924-5900 x142

On Tue, 29 Nov 2005 09:06:55 -0800, "Meg" wrote:

still doesn't work..
I'm very new to excel, so I don't really know exactly how to code this.

this is what I have in the column where I want to format the number
=CONCATENATE(VALUE(Q5),VALUE(R5))
yet, when I go to format it, doesn't take.


Meg


You're very close. Use:

=VALUE(CONCATENATE(Q5,R5))

or the equivalent:

=--(CONCATENATE(Q5,R5))


--ron

try to replace
=CONCATENATE(VALUE(Q5),VALUE(R5))
with
=VALUE(CONCATENATE(VALUE(Q5),VALUE(R5)))
and then format
Format Cells... Special Phone Number
--
Gary's Student


"Meg" wrote:

still doesn't work..
I'm very new to excel, so I don't really know exactly how to code this.

this is what I have in the column where I want to format the number
=CONCATENATE(VALUE(Q5),VALUE(R5))
yet, when I go to format it, doesn't take.


Meg

"Gary''s Student" wrote in message
...
This is a common problem:

if 1234569876 is a number then Format Cells... Special Phone Number
will work. It won't work if 1234569876 is a text string.

If 1234569876 is a text string in say A1 then use =value(A1) and format
it.
--
Gary''s Student


" wrote:

Hi,


I'm trying to format a column of cells that were created by combining 2
different cells using concatenate command.


The result is a phone number, but when I go to format it (so it
displays as a phone number (###) ###-####), nothing happens.


I'm using Excel 97 (don't laugh), but this is the case on XP as well


Thanks




--
Meg Darnell, LMT, CD
Director of Alumni Services
Swedish Institute College of Health Sciences
212-924-5900 x142

Hi,


I'm trying to format a column of cells that were created by combining 2
different cells using concatenate command.


The result is a phone number, but when I go to format it (so it
displays as a phone number (###) ###-####), nothing happens.


I'm using Excel 97 (don't laugh), but this is the case on XP as well


Thanks




--
Meg Darnell, LMT, CD
Director of Alumni Services
Swedish Institute College of Health Sciences
212-924-5900 x142

thanks, what is the format for phone numbers though...

--


"Bob Phillips" wrote in message
...
Change the concatenate formula to put the format

=TEXT(A1&B1,"format")

--

HTH

RP
(remove nothere from the email address if mailing direct)


" wrote in
message ...
Hi,


I'm trying to format a column of cells that were created by combining 2
different cells using concatenate command.


The result is a phone number, but when I go to format it (so it
displays as a phone number (###) ###-####), nothing happens.


I'm using Excel 97 (don't laugh), but this is the case on XP as well


Thanks




--
Meg Darnell, LMT, CD
Director of Alumni Services
Swedish Institute College of Health Sciences
212-924-5900 x142

still doesn't work..
I'm very new to excel, so I don't really know exactly how to code this.

this is what I have in the column where I want to format the number
=CONCATENATE(VALUE(Q5),VALUE(R5))
yet, when I go to format it, doesn't take.


Meg

"Gary''s Student" wrote in message
...
This is a common problem:

if 1234569876 is a number then Format Cells... Special Phone Number
will work. It won't work if 1234569876 is a text string.

If 1234569876 is a text string in say A1 then use =value(A1) and format
it.
--
Gary''s Student


" wrote:

Hi,


I'm trying to format a column of cells that were created by combining 2
different cells using concatenate command.


The result is a phone number, but when I go to format it (so it
displays as a phone number (###) ###-####), nothing happens.


I'm using Excel 97 (don't laugh), but this is the case on XP as well


Thanks




--
Meg Darnell, LMT, CD
Director of Alumni Services
Swedish Institute College of Health Sciences
212-924-5900 x142

thanks!
it worked!!!


--
Meg Darnell, LMT, CD
Director of Alumni Services
Swedish Institute College of Health Sciences
212-924-5900 x142

"Ron Rosenfeld" wrote in message
...
On Tue, 29 Nov 2005 09:06:55 -0800, "Meg"
wrote:

still doesn't work..
I'm very new to excel, so I don't really know exactly how to code this.

this is what I have in the column where I want to format the number
=CONCATENATE(VALUE(Q5),VALUE(R5))
yet, when I go to format it, doesn't take.


Meg


You're very close. Use:

=VALUE(CONCATENATE(Q5,R5))

or the equivalent:

=--(CONCATENATE(Q5,R5))


--ron

meg

Concatenation changes the cell format to Text.

The phone number format will not work on Text.

You must change the formula result to a value first.

Select range of formulas then

CopyPaste SpecialValuesOKEsc.

Still Text so copy an empty cell, select cells and Paste SpecialAddOKEsc

Now format as phone numbers.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 08:35:43 -0800, "
wrote:

Hi,


I'm trying to format a column of cells that were created by combining 2
different cells using concatenate command.


The result is a phone number, but when I go to format it (so it
displays as a phone number (###) ###-####), nothing happens.


I'm using Excel 97 (don't laugh), but this is the case on XP as well


Thanks