How to format date as text without changing appearance?
 

I have a column of 10900 cells with dates of the form
13-May-05. Excel automatically interpreted them as
dates when I imported the data from a text file. Now
I want them to be interpreted as text. The problem is:
when I change the format to "text", the cell content
changes to a number. For example, 13-May-05 becomes
38485.

Hindsight being 20-20, I should have entered the dates
in the form '13-May-50. But it is too late for that
now, I think.

How can I change the cell format to the text 13-May-05
without manually changing all 10900 cells?

Hi

I reckon you'll either need someone on the NG to write you a macro to do
this job in situ, or you can use a helper column, alongside the existing
column and then replace the column with the new one.
In the helper column use:
=TEXT(A2,"dd-mmm-yy")
and fill this down. Once you are happy with the result, copy the range and
Edit|Paste Special|Values to replace your formulas with the actual values.
Then you can replace the current column with the new one.
Make sure you take a copy of your data before you start!

--
Andy.


wrote in message
oups.com...
I have a column of 10900 cells with dates of the form
13-May-05. Excel automatically interpreted them as
dates when I imported the data from a text file. Now
I want them to be interpreted as text. The problem is:
when I change the format to "text", the cell content
changes to a number. For example, 13-May-05 becomes
38485.

Hindsight being 20-20, I should have entered the dates
in the form '13-May-50. But it is too late for that
now, I think.

How can I change the cell format to the text 13-May-05
without manually changing all 10900 cells?

<Andy B wrote:
In the helper column use: =TEXT(A2,"dd-mmm-yy")
[....] copy the range and Edit|Paste Special|Values
to replace your formulas with the actual values.

Yes. Great! That does solve the problem I asked about.
Thanks.

But now I have a Catch-22. I need the dates to be treated
as text because I want to have gaps in the sequence --
adjacent cells that are not adjacent dates.

But when I select that column (after applying your
suggestion) as the X-axis for a chart, the X-axis labels
appear as ordinal numbers 1, 2, etc.

I wanted the cell context (dates) to appear as X-axis
labels. They appeared that way when the cell value was
interpreted as in "date" format. (Klunk!)

Can I have cake and eat it, too?

Hi, Joe

Try pasting this into a module sheet:

Sub AddApostropheToRange()
Dim CellToModify As Object
For Each CellToModify In Selection
CellToModify.Formula = "'" & CellToModify.Formula
Next CellToModify
End Sub

Then, select all the cells you want to modify, hit Alt-F8 and run the macro.

Hope this helps

Pete



" wrote:

I have a column of 10900 cells with dates of the form
13-May-05. Excel automatically interpreted them as
dates when I imported the data from a text file. Now
I want them to be interpreted as text. The problem is:
when I change the format to "text", the cell content
changes to a number. For example, 13-May-05 becomes
38485.

Hindsight being 20-20, I should have entered the dates
in the form '13-May-50. But it is too late for that
now, I think.

How can I change the cell format to the text 13-May-05
without manually changing all 10900 cells?

Hi

If you just want the date, you could make the extra column using
=TEXT(A2,"dd")
leave the column there and use that for your graph.

--
Andy.


wrote in message
oups.com...
<Andy B wrote:
In the helper column use: =TEXT(A2,"dd-mmm-yy")
[....] copy the range and Edit|Paste Special|Values
to replace your formulas with the actual values.

Yes. Great! That does solve the problem I asked about.
Thanks.

But now I have a Catch-22. I need the dates to be treated
as text because I want to have gaps in the sequence --
adjacent cells that are not adjacent dates.

But when I select that column (after applying your
suggestion) as the X-axis for a chart, the X-axis labels
appear as ordinal numbers 1, 2, etc.

I wanted the cell context (dates) to appear as X-axis
labels. They appeared that way when the cell value was
interpreted as in "date" format. (Klunk!)

Can I have cake and eat it, too?