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.


<[email protected]> wrote in message
news:[email protected]...
>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



"[email protected]" 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.


<[email protected]> wrote in message
news:[email protected]...
> <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?
>