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?

Myrmecophagous

Excel is considered programming these days? To me this sounds off-topic. – Falstaffian

You mean with standard Excel - no VBA? – Kathlenekathlin

Yes, I'm talking about standard Excel. I've seen other non-VBA Excel questions here, so I assumed it would be fair game to ask here. – Myrmecophagous

As a starter you may get information about the formatting via =cell("format"; A1). – Kathlenekathlin

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

Erratic

As stated in the original question, yes, I know that it's stored as a number. However, my question was whether or not it was possible to access a pre-formatted value from another cell, be it time, currency, dates, fractions, scientific notation, ISBN numbers or whatever it's formatted as. – Myrmecophagous

According to the comments he looks for a non-VBA answer. – Kathlenekathlin

@Kathlenekathlin sometimes much easier to use some programming to solve a problem :) – Erratic

Should be =TEXT(A1;"hh:mm:ss") - ( semicolon instead comma) – Neighborly

Answer 2

So that does not quite work as the VBA Format function isn't compatible with Excel formats. The table below shows the difference between "GetString()" above, and "GetText()"

Public Function GetText(ByVal cell As Range) As String
 GetText = Application.WorksheetFunction.Text(cell, cell.NumberFormat)
End Function

Short Date and Long date are interesting -- they are off by 1 day.

Format      Value                       GetString                   GetText                    GetFormat
general     3.141592638                 'Ge23eral'                  '3.141592638'              'General'
number      3.14                        '3.14'                      '3.14'                     '0.00'
Currency    $3.14                       '$3.14'                     '$3.14'                    '$#,##0.00'
Accounting  $3.14                       '_($3.14_)'                 ' $3.14 '                  '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'
Short Date  1/3/1900                    '1/2/1900'                  '1/3/1900'                 'm/d/yyyy'
Long Date   Tuesday, January 3, 1900    'Tuesday, January 02, 1900' 'Tuesday, January 3, 1900' '[$-F800]dddd, mmmm dd, yyyy'
Time        3:23:54 AM                  '3:23:54 AM'                '3:23:54 AM'               '[$-F400]h:mm:ss AM/PM'
Percentage  314.16%                     '314.16%'                   '314.16%'                  '0.00%'
Fraction    3  2/16                     '3 ??/16'                   '3  2/16'                  '# ??/16'
Scientific  3.14E+00                    '3.14E+00'                  '3.14E+00'                 '0.00E+00'
Text        3.141592638                 '3.141592638'               '3.141592638'              '@'

Isobar