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

[Guest]

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?

 

[Bob Phillips]

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

as an example

--

HTH

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

 

[Frank Kabel]

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

 

[Guest]

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

 

[Guest]

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.

 

[asynmyx]

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?

 

[Ron Rosenfeld]

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?

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

 

[Gord Dibben]

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

Note the <space> added after yyyy


Gord Dibben MS Excel MVP