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.