How to directly get formatted cell values?

[Genom]

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!

[JohnSUN]

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]

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")

[Genom]

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?

[dandv]

@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.