We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to directly get formatted cell values? [closed]

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

Genom gravatar image


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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-09 15:31:57.117488


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

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
    getCellText = "<wrong parameter>"
End Function


edit flag offensive delete link more


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

@Genom: this might happen over the course of a few years, if you file a bug requesting this enhancement. Just posting here won't make that happen.

dandv gravatar imagedandv ( 2020-10-25 07:01:41 +0200 )edit

Question Tools

1 follower


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

Seen: 1,649 times

Last updated: Jul 10 '17