Your code isn't ever touching the data, it's just putting formulas into the cells, then replacing the formula with the resulting value. Also, your second assignment statement is wiping out the first. Also, your formulas are always going to refer to cell A1 regardless of what row the new data is on. Maybe you are unclear on exactly how this kind of code works.
This might be more effective if you modify your VBA to do what you actually want, instead of using VBA to plug in formulas to cells. String manipulation is a little more sophisticated in VBA than what you can do with Excel functions (but only a little).
Here is a start. It is difficult to do this without seeing sample data. This assumes that the data you're starting with is in column A in the last row, and the cleaned-up version goes into column B. I didn't test it because I don't know exactly what your data looks like. I gleaned this from your formulas.
Bookmarks