On Thursday May 4 2006 04:48 am, Ville Mattila wrote:
> Hello,
>
> I am trying to create a spreadsheet that contains some formatted
> information in cells (for example special time format HHMM and so on). I
> would like to create a concatenated string from the formatted values of
> the cells and some static texts.
>
> Example
> A B C
> 1 0920 My Event = CONCATENATE("At "; A1; " "; B1)
>
> The result on C1 is like "At 373918,2331441 My Event" even I would like
> it to be "At 0920 My Event".
>
> How this should be done?
>
> Thanks,
> Ville
>
Concatenate is designed to work with cells containing text. You have
cell A1 formatted as a Time.
What you need is
=CONCATENATE("At ";CONCATENATE(HOUR(A1);MINUTE(A1);" ";B1)). This generates
this: At 920 My Event. If you need the zero (0) in front of the 920, you
will have to use two more columns.
A B C D
1 09:20 My Event =HOUR(A1) =MINUTE(A1)
Then format Column C as a number with no decimal places and 2 leading
zeroes. Then the formula becomes
=CONCATENATE("At ";CONCATENATE(HOUR(C1);MINUTE(D1));" ";B1)
Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
