Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
DaveAg02
 
Posts: n/a
Default 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?
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

=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?



  #3   Report Post  
Frank Kabel
 
Posts: n/a
Default

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?


  #4   Report Post  
DaveAg02
 
Posts: n/a
Default

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?




  #5   Report Post  
Peter
 
Posts: n/a
Default 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?






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default 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?




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 11:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"