Ask Your Question
0

How to directly get formatted cell values?

asked 2017-07-10 16:25:21 +0200

Genom gravatar image

Hi,

I use formatted cells. For instance the cell value is: 1 and cell format is: "Day "Standard. So the cell displays: "Day 1". Now I would like to get this formatted value as a string/text to be used in a formula (e.g. concatenation). This question was asked before but the answer isn't really a solution. I wonder, if there is any way to do this? There is the CELL function, but the only info_type, which is not available is the formatted value.

Thanks for all suggestions!

edit retag flag offensive close merge delete

Comments

If you are not satisfied with the variant like as =TEXT(A1;"""Day ""Standard"), then you will have to accept - except as a macro (the User described function) you can not get this value.

JohnSUN gravatar imageJohnSUN ( 2017-07-10 17:25:01 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2017-07-10 17:36:49 +0200

JohnSUN gravatar image

If you ready use macro then try this

Function getCellText(sAddress As String) As String
Dim oSheets As Variant
Dim oCellRangesByName As Variant
Dim oXCellRange As Variant
Dim oCellByPosition As Variant
    On Error GoTo Wrong_parm
    oSheets = ThisComponent.getSheets()
    oCellRangesByName = oSheets.getCellRangesByName(sAddress)
    oXCellRange = oCellRangesByName(0)
    oCellByPosition = oXCellRange.getCellByPosition(0, 0)
    getCellText = oCellByPosition.getString()
    Exit Function
Wrong_parm:
    getCellText = "<wrong parameter>"
End Function

Usage

=GETCELLTEXT("D1")
=GETCELLTEXT("D"&ROW())
=GETCELLTEXT("Sheet2.D8")
edit flag offensive delete link more

Comments

Thanks, I was looking for a solution with built-in functions. I wonder, if macros like these (I have seen many missing functions like this one covered with Macros) can be permanently incorporated into Libre Calc by the developers?

Genom gravatar imageGenom ( 2017-07-12 10:21:48 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-10 16:25:21 +0200

Seen: 680 times

Last updated: Jul 10 '17