Ask Your Question
2

How to directly concatenate formatted cell values? [closed]

asked 2012-06-08 09:44:04 +0200

Ralf Wohner gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-16 05:09:53.617840

Comments

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

littlerunningdeer gravatar imagelittlerunningdeer ( 2013-12-25 22:53:54 +0200 )edit

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

Genom gravatar imageGenom ( 2017-07-10 16:19:11 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2012-06-08 10:45:08 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more

Comments

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

Ralf Wohner gravatar imageRalf Wohner ( 2012-06-08 16:03:16 +0200 )edit
1

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

Ralf Wohner gravatar imageRalf Wohner ( 2012-06-08 16:04:39 +0200 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2012-06-09 03:42:29 +0200 )edit

@Ralf Wohner -- Still looking for an answer here?

qubit gravatar imagequbit ( 2013-03-13 09:27:20 +0200 )edit

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

Genom gravatar imageGenom ( 2017-07-10 16:18:39 +0200 )edit

Question Tools

Stats

Asked: 2012-06-08 09:44:04 +0200

Seen: 48,152 times

Last updated: Jun 08 '12