Geek Answers Handbook

Reference cell value as string in Excel

In Excel, if cell A1 has some value that is formatted in a certain way, is there a way that cell B1 refers to a row displayed in A1?

To clarify:

  • If A1 displays, for example, the time 10:31:48, I want B1 to refer to this output string, as shown to the user ("10:31:48", and not the base numeric representation "0.43875").
  • I am well aware that there are functions for formatting manually. However, what I'm looking for is copying an already formatted value from another cell, no matter what format this cell may have.

Is this possible?

+7
source share
1 answer

In fact, Excel stores the datetime as number , so you need to explicitly specify the cell format in order to see the correct value.

You can use the TEXT function, but in any case you need to specify the format output line:

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

Another option is to write your own VBA function that can convert the cell value based on this 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 the format of the source cell

+6
source

More articles:


All Articles