How to directly concatenate formatted cell values?

[RalfWohner]

Hello.

How can I concatenate formatted cell values as they are displayed, without having to manually re-do the formatting for each concatenated cell?

Example:

           A1             A2       A3
 Content   31.12.2012     =1/3     ="On the " & A1 & " we had " & A2 & " times as many."
 Result    31.12.12       0.33     On the 41274 we had 0.33333333333333 times as many.
 Expected  31.12.12       0.33     On the 31.12.12 we had 0.33 times as many.

(Let’s assume A2 is formatted as “0.00” and the date format is German … that’s just an example.)

If there was a formula like “formattedValue()” which hands over the displayed value of a cell, I could rewrite it like:

="On the " & formattedValue(A1) & " we had " & formattedValue(A2) & " times as many."

which would be fine. I just couldn’t find anything like that.

Can anyone help?

Thanks,
Ralf

[littlerunningdeer]

This is a well posed, usually misinterpreted, question. I am interested too.

[Genom]

5 years later I am trying to do the same thing but there is no way?!

[mariosv]

In US English format:
=“On the " & TEXT(A1;“dd.mm.yy”) & " we had " & TEXT(A2;“0.00”) & " times as many.”

Take a look at:
http://help.libreoffice.org/Common/Number_Format_Codes/de

[RalfWohner]

Yes, I know that, but that assumes I know the format of each individual cell and it won’t change.

[RalfWohner]

Is there a way to READ the format String of a cell, so that I could use … & TEXT(A1;GETFORMAT(A1)) & … ?

[mariosv]

The function CELL(“FORMAT”;address) can give what type of format the cell have but not the detailed format. See the function in LibreOffice help to know what can you get.

[qubit1]

@RalfWohner – Still looking for an answer here?

[Genom]

I am still looking for an answer. It is a bit ridiculous that there is no way of doing this?!