Reference cell value as string in Excel

Question

In Excel, if the cell A1 has some value that gets formatted in a specific way, is there a way for cell B1 to reference the string displayed in A1?

To clarify:

• If A1 displays, for instance, the time 10:31:48, I wish to have B1 reference this outputted string as shown to the user ("10:31:48", not the underlying numerical representation "0.43875").
• I'm well aware that there are functions for manually formatting values. However, what I'm looking for is copying an already formatted value from another cell, no matter what format that cell may have.

Is something like this possible?

Answer 1

In fact, Excel stores datetime as a number, so you have to explicitly set format of the cell to see the proper value.

You may want to use TEXT function, but anyway, you have to specify format of output string:

=TEXT(A1,"hh:mm:ss")

Another option is to write your own VBA function, which can convert a value of a cell based on it's format:

Public Function GetString(ByVal cell As Range) As String
 GetString = Format(cell, cell.NumberFormat)
End Function

This will give you a result based on source cell's format