I have a whole column of dates nicely formatted the way I want (eg
"Wednesday 28 December 2011"). I want to concatenate those dates with an
another column of text. When I do that, Excel 2003 treats the dates as
numbers, and I end up with (for example)
40400 <followed by the text I added>

Whereas what I wanted was
Wednesday 28 December 2011 <followed by the text I added>

Hope that makes sense, and your help would be much appreciated please.




--
Register as an organ donor with the NHS online. It takes 1 minute and saves
you carrying an organ donor card with you.
http://www.uktransplant.org.uk/ukt/h...me_a_donor.jsp

GB wrote:
> I have a whole column of dates nicely formatted the way I want (eg
> "Wednesday 28 December 2011"). I want to concatenate those dates with
> an another column of text. When I do that, Excel 2003 treats the
> dates as numbers, and I end up with (for example)
> 40400 <followed by the text I added>
>
> Whereas what I wanted was
> Wednesday 28 December 2011 <followed by the text I added>
>
> Hope that makes sense, and your help would be much appreciated please.

I found this link, and the solution there works, but is there nothing more
straightforward?
http://office.microsoft.com/en-us/ex...003056124.aspx



--
Register as an organ donor with the NHS online. It takes 1 minute and
saves you carrying an organ donor card with you.
http://www.uktransplant.org.uk/ukt/h...me_a_donor.jsp

"GB" <(E-Mail Removed)> wrote:
> GB wrote:
>> I have a whole column of dates nicely formatted the way
>> I want (eg "Wednesday 28 December 2011"). I want to
>> concatenate those dates with an another column of text.
[....]
>> what I wanted was
>> Wednesday 28 December 2011 <followed by the text I added>
[....]
> I found this link, and the solution there works, but is
> there nothing more straightforward?
> http://office.microsoft.com/en-us/ex...003056124.aspx

Don't know you standard for "straigtht-forward". That's basic the way to do
it.

If your dates are in A1:A1000 and your additional text is in B1:B1000, put
the following formula into C1 and copy down through C1000:

=TEXT(A1,"dddd dd mmmm yyyy") & " " & B1

That assumes you want to separate the date and additional text by one space,
and the text in column B does not start with a space.

If you want the new values in C1:C1000 to overwrite either A1:A1000 or
B1:B1000, you can copy C1:C1000, select A1:A1000 for example, right-click
and click on Paste Special, select Values, then click OK.

If all the text in column B are the same, you could simply format A1:A1000
with the following Custom date:

dddd dd mmmm yyyy" yourText"

And you could dispense with either change by writing a macro. But that does
not meet __my__ definition of "straight-forward" ;-).

joeu2004 wrote:
> "GB" <(E-Mail Removed)> wrote:
>> GB wrote:
>>> I have a whole column of dates nicely formatted the way
>>> I want (eg "Wednesday 28 December 2011"). I want to
>>> concatenate those dates with an another column of text.
> [....]
>>> what I wanted was
>>> Wednesday 28 December 2011 <followed by the text I added>
> [....]
>> I found this link, and the solution there works, but is
>> there nothing more straightforward?
>> http://office.microsoft.com/en-us/ex...003056124.aspx
>
> Don't know you standard for "straigtht-forward". That's basic the
> way to do it.
>
> If your dates are in A1:A1000 and your additional text is in
> B1:B1000, put the following formula into C1 and copy down through
> C1000:
> =TEXT(A1,"dddd dd mmmm yyyy") & " " & B1
>
> That assumes you want to separate the date and additional text by one
> space, and the text in column B does not start with a space.
>
> If you want the new values in C1:C1000 to overwrite either A1:A1000 or
> B1:B1000, you can copy C1:C1000, select A1:A1000 for example,
> right-click and click on Paste Special, select Values, then click OK.
>
> If all the text in column B are the same, you could simply format
> A1:A1000 with the following Custom date:
>
> dddd dd mmmm yyyy" yourText"
>
> And you could dispense with either change by writing a macro. But
> that does not meet __my__ definition of "straight-forward" ;-).

Thanks for your helpful answer, confirming that's the way to do it.

--
Register as an organ donor with the NHS online. It takes 1 minute and
saves you carrying an organ donor card with you.
http://www.uktransplant.org.uk/ukt/h...me_a_donor.jsp