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

New Contributor

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







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?]




1 Reply

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.