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.
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):
3/1/71 12/01/1882 Fall 1988 1993
And if I choose one of those cells and right click and select formatting I will get different things:
m/d/yy General General General
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):
25993 12/01/1882 Fall 1988 1993
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.