How do I keep "text" style when globally replacing dates in Excel?

%3CLINGO-SUB%20id%3D%22lingo-sub-799121%22%20slang%3D%22en-US%22%3EHow%20do%20I%20keep%20%22text%22%20style%20when%20globally%20replacing%20dates%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-799121%22%20slang%3D%22en-US%22%3E%3CP%3EI%20keep%20a%20monthly%20log%20of%20electric%20meter%20readings%20in%20Excel.%20I%20track%20readings%20by%20date%2C%20and%20the%20first%20column%20is%20simply%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E01%2F01%3C%2FP%3E%3CP%3E01%2F02%3C%2FP%3E%3CP%3E01%2F03%3C%2FP%3E%3CP%3Eetc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20the%20default%20style%20automatically%20changes%20each%20date%20to%2001-Jan%20and%20so%20on%2C%20I%20format%20the%20column%20as%20%22text%22%20and%20it%20displays%20the%20way%20I%20want%20it%20to.%20So%20far%2C%20so%20good.%20But%20when%20I%20want%20to%20create%20the%20February%20page%2C%20I%20would%20like%20to%20copy%20the%20January%20date%20column%2C%20paste%20it%20into%20February%2C%20and%20then%20globally%20replace%20every%2001%20with%2002.%20Should%20be%20easy.%20But%20in%20practice%2C%20as%20soon%20as%20I%20click%20%22replace%20all%22%20the%20column%20immediately%20reverts%20to%20standard%20date%20format%20(01-Feb)%20which%20I%20do%20not%20want.%20I%20have%20tried%20a%20variety%20of%20kluges%20but%20none%20of%20them%20works.%20Is%20there%20any%20way%20to%20globally%20change%20the%20months%20in%20a%20column%20of%20text-style%20dates%20without%20losing%20the%20text%20style%3F%3C%2FP%3E%3CP%3E%5BNote%3A%20I%20am%20using%20Excel%202016%2C%20not%20Excel%20365.%20Do%20they%20work%20the%20same%3F%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDK%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-799121%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-802382%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20keep%20%22text%22%20style%20when%20globally%20replacing%20dates%20in%20Excel%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-802382%22%20slang%3D%22en-US%22%3E%3CP%3EOK%2C%20I%20found%20the%20forgotten%20shortcut.%20Just%20type%20an%20apostrophe%20at%20the%20beginning%20of%20a%20number%20string%20and%20it%20will%20display%20as%20text%2C%20and%20stay%20that%20way%20even%20when%20you%20copy%20and%20paste.%20Problem%20solved.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I keep a monthly log of electric meter readings in Excel. I track readings by date, and the first column is simply:

 

01/01

01/02

01/03

etc.

 

Since the default style automatically changes each date to 01-Jan and so on, I format the column as "text" and it displays the way I want it to. So far, so good. But when I want to create the February page, I would like to copy the January date column, paste it into February, and then globally replace every 01 with 02. Should be easy. But in practice, as soon as I click "replace all" the column immediately reverts to standard date format (01-Feb) which I do not want. I have tried a variety of kluges but none of them works. Is there any way to globally change the months in a column of text-style dates without losing the text style?

[Note: I am using Excel 2016, not Excel 365. Do they work the same?]

 

DK

 

1 Reply
Highlighted

OK, I found the forgotten shortcut. Just type an apostrophe at the beginning of a number string and it will display as text, and stay that way even when you copy and paste. Problem solved.