concatenate command

B

Bob Phillips

Change the concatenate formula to put the format

=TEXT(A1&B1,"format")

--

HTH

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

Gary''s Student

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

Ron Rosenfeld

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
Click to expand...

You're very close. Use:

=VALUE(CONCATENATE(Q5,R5))

or the equivalent:

=--(CONCATENATE(Q5,R5))


--ron
 
G

Gary''s Student

try to replace
=CONCATENATE(VALUE(Q5),VALUE(R5))
with
=VALUE(CONCATENATE(VALUE(Q5),VALUE(R5)))
and then format
Format > Cells... > Special Phone Number
 
M

meg\(removethis\)lmt

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
 
M

Meg

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
 
M

Meg

thanks!
it worked!!!


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

 
G

Gord Dibben

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

Copy>Paste Special>Values>OK>Esc.

Still Text so copy an empty cell, select cells and Paste Special>Add>OK>Esc

Now format as phone numbers.


Gord Dibben Excel MVP
 
Top