SUBSTITUTE Function for a range

Hi,
I need some help, I am trying to use the SUBSTITUTE formula to find any instance of a misspelled name and have it substitute in the correct spelling. This stems from an error where two areas I am pulling information from have slightly different spellings of the same name and I need them to match so that a another formula that pulls information from the sheet will function properly. So for example sheet A pulls information from sheet B and C and matches them, since the information in sheet B for this entry is spelled wrong is does not match. So how can I have it look for any instance of the misspelling in sheet B and replace it with the correct version? I thought it would be simpler and tried using
=SUBSTITUTE(A1:A500, "old_text","new_text")

So I am using the range of the area the name could appear in and had old text as the misspelled name, then the new text as the correct spelling I need it to be, but it just isn't working. I left out the instance number since I want it to correct any time it happens and that could vary depending on the day since this is in a template that is used daily. My problem may also be that, since I have to dump new data pulled from a CSV into this sheet everyday I didn't want the formula to be in the cells themselves (as they would be wiped out daily) and have it put in under conditional formatting for that range, using that formula.

Please help! This error is something I can not fix as it is incorrectly named from another source I have no control over and I do not wish to have to do a find and replace every day when using this template. Thanks.

ExampleWorkBook.xlsx

Okay I have attached an example to try and illustrate what I am working with. Everyday as part of an end of day retouine myself or another has to complete a much more detailed version of this workbook. In the "CSV_1" sheet we paste in information from a CSV of what information is *supposed* to be used for the day in question. In "CSV_2" we paste in information from a CSV of what *actually* was used. Then on the "Daily_Checkout" we confirm if they match or not. You will see green "Y" means they match, red means they do not match. But the issue in question shows up as "#N/A" because of the difference in the name in the two CSV areas. In one the name ends with ".net" where in the other (and all other sheets for other areas that are not included) it ends with ".com" The issue only arises in this sheet because of an issue with a certain CSV, this CSV is provided by another company I work with who is aware of the issue but does not care to correct it as they do not see it as causing any issues.

Hopefully this helps, again since this is something we must every do everyday to ensure the work we do is completed correctly it would be helpful to have something that automatically changes any instance of the name in "CSV_1" to the correct name so that on "Daily_Checkout" we could easily see if they match or not.

Originally Posted by popipipo

Try to do it this way:
CTRL H Find (.net) and replace (.com)

As stated in the original post I would like to avoid having to find and replace everyday and would like to find a way that it would automatically replace all occurrences