Stop autoformating from text to custom date.

dwrmann

New Member
Joined
Nov 22, 2005
Messages
1
Excel is automatically reformatting cells from text to date. I have cells that I want as text that are "Oct-97", "Nov-97", etc. I want the retained as text so I can load the excel sheet into Pagemaker. As dates, the text is too large for my cells causing the "###" to be in the cell. As text, there is no problem.

I am using VBA to read "Oct-97" from one worksheet and placing it into another worksheet as text. Excel automatically converts the format from text to Custom "mmm-yy". I do not want this.

So how can I stop excell from changing my cell formats?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,138
Office Version
365
Platform
Windows
Hmmm... I am having problems recreating your problem.

Can you post your VBA code?
 

Ian_B

New Member
Joined
Nov 11, 2006
Messages
8
Stumbled across this thread while looking for an answer to the same question (which I haven't found)...

When you enter, for example, 1-3 into a cell, it gets converted to a date 01-Mar automatically, but I want to preserve it in the format as originally typed.

I know if you preceed the entry with an apostrophe e.g. '1-3, the text is preserved, but my spreadsheet is being distributed to people where only some have knowledge of this function.

So, does anyone have a handy bit of VBA* that will preceed my text string with an apostrophe automatically, but ignore the entry if someone enters a text string beginning with an apostrophe? Or if this a cumbersome way of doing it, and alternative solution!

many thanks,

Ian

*VBA isn't my strong point, but I'm trying to learn :oops:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
Howard

Are these 'real' dates?

How are they being copied?

Ian

Try formatting the range/cell as Text before you enter anything in the cell.

Note you should really have started a new thread, I think your problem is quite different to Howard's.
 

Forum statistics

Threads
1,078,006
Messages
5,337,694
Members
399,162
Latest member
Sabina

Some videos you may like

This Week's Hot Topics

Top