Using Find and Replace, macro to physically reformat a cell to comfort to 1 type of format

[bearcub]

I'm working on a project where I might have to a macro run at a specific time of day.

The data I'm using is coming from an external database where a number of fields have data that is not formatted properly. For example, in the primary telephone field I have 32 different formats. I need to clean the data so that one data type occurs. We will eventually be exporting the cleaned data back into the DB but for now we have to maintain our own DB. I looked into using PowerQuery to clean the data but I don't know how many different find and replace commands it can handle.

I just started this project this week. I'm trying to figure out all the options available to me before I start putting time into creating the project tools.

The macro would be comprised of three different tasks

1 task would be to connect to an external database table and import it into Excel.

The next task would be to clean the data with a series of macros (maybe 7 or more different reformatting macros)

Export this cleaned data into an Access table.

Import the Access table back into Excel.

I thought about using powerquery but I don't think it could fix most of the issues. The other option I have is to create mega formulas or create a macro to run behind the scene.

My end users will be office admin who don't know much about Excel or have enough time in their day to reformat the data.