How can I get the displayed value of a cell in MS Excel ( for text that was converted to dates)?

Excel File Origin

I have an Excel file that was originally a CSV file. At this time I don't have access to the original CSV file. When the CSV file was originally opened in Excel, many of cells in the Date column were automatically converted.

Example Values

Here is an example of some of the items from the Date column ( spacing represents left and right aligned cells, mostly to show that the second date was not converted):

exhibit 1:

Fall 1988

And if I choose one of those cells and right click and select formatting I will get different things:


Possible Logic

So looks like excel hasn't converted the 12/01/1882 date - maybe because it is "too old" or maybe for another reason.

If I select the whole column and change it to "General" formatting it then gives me this ( text left aligned, numbers right aligned):

Fall 1988

What I Want

I'd like to have one column where I have the format set to "General" or "Text" and it would have the dates that were displayed from exhibit 1 above.

I saw this question about using a DataFormatter class (looks like Java?) and some similar questions that use Macros and VB scripting - but if at all possible I would really like a way to use an excel function to do this.

What I've tried

I've tried using these things which have not worked:

CONCATENATE - to get the date field, and prepend it with a space
INDIRECT(ADDRESS(ROW(),5)) - get the value indirectly
VALUE() - get the "value' of the cell - opposite of what I want
TEXT() - can explicitly specify format but still can't make it work
CELL() - can be used to get cell information such as "contents" - no luck with this but I did see that I could use it also to get format which would tell me if the cell is displayed as a number or as "general" which could then possibly used with an IF function somehow to convert a number that is displayed as a date back to an actual date... possibly?

Copy Paste Method

One method I saw which seems to work is to select the values, copy and paste them into notepad, and then copy and paste them back into excel (it effectively gives the displayed value - which is what I want )

The only problem is that I would like to have a function to do this because the excel sheet has a few hundred thousand rows in it.


If you have shown us the full range of possible formats, this should work:

=TEXT(A1, IF(CELL("format",A1)="D4", "m/d/yy", "General"))

If you have date formats other than m/d/yy, add tests for them.

  • 1
    Not a lot of explanation, and doesn't seem to solve any of the general problem of getting what is displayed on screen as the result of a formula. Possibly a help in guessing which unrelated pre-canned format to use but not sure how it adds to the question. – ebyrob Jul 27 '17 at 15:37
  • (1) Is there some specific part of this that you don’t understand? (2) What do you mean by “the general problem of getting what is displayed on screen as the result of a formula”? (3) OK, maybe this doesn't solve some general problem that you have.  I said in my post that it was tailored to the data in the question. (4) Answers aren’t supposed to add to questions; they are meant to answer them.  The OP seemed to think that this was adequate.  Can you explain why you think it isn’t? – Scott Jul 27 '17 at 21:05
  • 1
    Why, not how or what, seems important and missing. I apologize for being abrupt. – ebyrob Jul 28 '17 at 14:03

A general answer:

Rather than trying to fuss with the results of the CELL("format"...) function, it may be easier to use a user-defined function to return the NumberFormat property of the cell directly.

In that case, the expression "=TEXT(A1,NumberFormat(A1))" would give you the displayed value rather directly.

To enable this, you need the following in a module of the worksheet:

  Public Function NumberFormat(CellRange As Range) As String

  NumberFormat = CellRange.NumberFormat

  End Function

The same answer but with a different function (that has worked for me):

Public Function DisplayText(ByVal pRange As Range) As String  
  DisplayText = pRange.Text  
End Function  

Just use =DisplayText(A1). If you change the cell format this function will return the displayed text


This works for me. It is the same as the post above but includes an error check:

Step 1: Copy and paste the following public function in a VBA module:

Public Function CELLTEXT(target As Range) As String
   If target.Cells.Count > 1 Then
    MsgBox "CELLTEXT function requires a single cell as an input, please try again"
     Exit Function
        CELLTEXT = target.Text
    End If
End Function

Step 2: Try it in your spreadsheet. In any cell enter the formula, for example: =CELLTEXT(A1). This will then output whatever A1 was displaying.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.