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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
=TEXT(A1,"dd/mm/yyy") & TEXT(B1,"#,##0.00")

as an example

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thank you! I should have been able to figure that one out, but I knew someone
would be able to help me here!
-Dave
 
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.
 
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?
 
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?
Click to expand...

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
 
=TEXT(A1,"dd/mm/yyyy ") & TEXT(B1,"hh:mm:ss")

Note the <space> added after yyyy


Gord Dibben MS Excel MVP
 
Back
Top