How do I concatenate information in Excel and keep the number form
 

I've been using the concatenate function in Excel to make text strings out of
multiple cells, and I'm trying to do so for cells that include dates and
times. When I concatenate these cells and they are converted to text, I get
the general number format for the dates and times instead of the formatted
date or time that I've entered. Is there a way to avoid this?

=TEXT(A1,"dd/mm/yyy") & TEXT(B1,"#,##0.00")

as an example

--

HTH

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


"DaveAg02" wrote in message
...
I've been using the concatenate function in Excel to make text strings out
of
multiple cells, and I'm trying to do so for cells that include dates and
times. When I concatenate these cells and they are converted to text, I
get
the general number format for the dates and times instead of the formatted
date or time that I've entered. Is there a way to avoid this?

Hi
use something like
=A1 & TEXT(A2,"YYYY/MM/DD")

--
Regards
Frank Kabel
Frankfurt, Germany

"DaveAg02" schrieb im Newsbeitrag
...
I've been using the concatenate function in Excel to make text
strings out of
multiple cells, and I'm trying to do so for cells that include dates
and
times. When I concatenate these cells and they are converted to text,
I get
the general number format for the dates and times instead of the
formatted
date or time that I've entered. Is there a way to avoid this?

Thank you! I should have been able to figure that one out, but I knew someone
would be able to help me here!
-Dave

"Bob Phillips" wrote:

=TEXT(A1,"dd/mm/yyy") & TEXT(B1,"#,##0.00")

as an example

--

HTH

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


"DaveAg02" wrote in message
...
I've been using the concatenate function in Excel to make text strings out
of
multiple cells, and I'm trying to do so for cells that include dates and
times. When I concatenate these cells and they are converted to text, I
get
the general number format for the dates and times instead of the formatted
date or time that I've entered. Is there a way to avoid this?

How do I concatenate information in Excel and keep the number
 

I used Bob Phillips example but modified it to read:
=TEXT(B:B,"dd/mm/yyy") & " " & TEXT(C:C,"hh:mm:ss")
as I did not need it for the functionality but for appearance.


"Bob Phillips" wrote:

=TEXT(A1,"dd/mm/yyy") & TEXT(B1,"#,##0.00")

as an example

--

HTH

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


"DaveAg02" wrote in message
...
I've been using the concatenate function in Excel to make text strings out
of
multiple cells, and I'm trying to do so for cells that include dates and
times. When I concatenate these cells and they are converted to text, I
get
the general number format for the dates and times instead of the formatted
date or time that I've entered. Is there a way to avoid this?

How do I concatenate information in Excel and keep the number
 

I tried Bob Phillip's formula and it works except the date and time do not
have a space between them for easier reading. I also tried to suggestion
after that and it did not work. Any suggestions?
--
ELG


"Peter" wrote:

I used Bob Phillips example but modified it to read:
=TEXT(B:B,"dd/mm/yyy") & " " & TEXT(C:C,"hh:mm:ss")
as I did not need it for the functionality but for appearance.


"Bob Phillips" wrote:

=TEXT(A1,"dd/mm/yyy") & TEXT(B1,"#,##0.00")

as an example

--

HTH

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


"DaveAg02" wrote in message
...
I've been using the concatenate function in Excel to make text strings out
of
multiple cells, and I'm trying to do so for cells that include dates and
times. When I concatenate these cells and they are converted to text, I
get
the general number format for the dates and times instead of the formatted
date or time that I've entered. Is there a way to avoid this?

How do I concatenate information in Excel and keep the number
 

On Fri, 9 May 2008 09:23:01 -0700, asynmyx
wrote:

I tried Bob Phillip's formula and it works except the date and time do not
have a space between them for easier reading. I also tried to suggestion
after that and it did not work. Any suggestions?
--
ELG

The thread to which you refer seems to be several years old.

It would be helpful if you posted a sample of your data, and your expected
output.
--ron

How do I concatenate information in Excel and keep the number
 

=TEXT(A1,"dd/mm/yyyy ") & TEXT(B1,"hh:mm:ss")

Note the <space added after yyyy


Gord Dibben MS Excel MVP

On Fri, 9 May 2008 09:23:01 -0700, asynmyx
wrote:

I tried Bob Phillip's formula and it works except the date and time do not
have a space between them for easier reading. I also tried to suggestion
after that and it did not work. Any suggestions?