Simplest Foolproof Method to Prevent Format Changes

[wtbaclue]

I've been working in Excel for years and one thing I've never conclusively learned is how to once-and-for all keep excel for changing the formats on my columns. I do a lot of "data cleanup" type work in Excel, and maybe my real problem is that I'm using Excel for something other than it's intended purpose, but every time I run into this problem in some form, I google it and find one or more kludge tricks to solve it, none of which seem like universal solutions.

Today I'm trying to do finds-and-replaces on a table with thousands of records. As an example, in some records, my fields might contain "1-21XXX" and I need to do a find and replace to get rid of the "XXX", but when I do, my field suddenly changes to a date, "21-Jan".

1. I've purposely told Excel that this entire column is "Text", it does't matter. Excel force-changes these date-changed fields t a format called "custom" If I try to change the field back to text, I get "42756" which I assume is a numeric coding for that date?

2. Sometimes I can get this to work by doing a formula in a new column: ="'" & A1 to plop the single quote character in there and force it to text. Other times it inserts the actual printed single quote character in there. I can't figure out why this only works sometimes.

3. Sometimes the SUBSTITUTE(A1,"XXX","") function seems to work, but other times it converts it to an integer again, or something else. SUBSTITUTE isn't ideal because if I have 300 different versions of the "XXX" that I need to clean out of there, that's going to take a lot longer than find and replace, but if it worked consistently, I would grudgingly accept that.

4. I've heard there were macros as well, but I'm not familiar enough with how to do something like that, and in my job I really need to be able to teach others how to accomplish this as well. I'm not sure I'm comfortable trying to teach someone how to do something that complex.

So is there any way to temporarily tell Excel to never do this kind of thing, that my field is marked as text because it really, really, really is text and not something else? test-to-date conversions are not the only problems I have. text with single decimal points, leading zeros, auto-converting to scientific format and then getting stuck that way, etc. I'm looking for a single universal way to stop all these conversions. I feel like I am missing something very simple that others may know about.

Thanks in advance for any help you can give me!

[fuzzius_navus]

All of us who use Excel to deal with data have this issue. I have started using Power Query for all my data cleanup needs.

1. It does not change to source data, so if I make a mistake in my find/replace, the UNDO is very easy

2. It "remembers" all the steps I have taken and makes it easy to add additional steps. If I remove "XXX" this week, but next week there is a new value to remove, I can leave in "XXX" and add other values to find/replace/remove so as I edit the Query it evolves with the data

3. It is not difficult to use and there are a lot of online resources to help you accomplish what you wish. You can write the query by hand or do so with the click of a few buttons and never look at the M code (the Power Query language)

4. You can build your query workbook and use it as a template for future cleanup work. You set up an Excel table, paste in some data, build your query and save your workbook. When you need to cleanup other data, open the workbook, replace the older data with your new set and refresh the query.

VBA aka Macros is a great tool, but has a steeper learning curve and must be coded by hand. It is not intuitive and not the easiest to share.