How to remove the content of one cell and replace it with the content of another

Hey all,
In this formula:

Please Login or Register  to view this content.

what happens is I'm trying to replace this:

SUBSTITUTE(A1,A1,B1)

with an excel formula that won't substitute the content in the column which this function is called, but will rather replace all the content of cell A1 with all the content of cell B1 leaving B1 completely empty.

I tried the REPLACE but it only gave me some kind of #REF value.

Thanks for response.

This not possible to do with formulas? Will I be required to use VBA?

So I made this macro myself:

Please Login or Register  to view this content.

It seems to work, but obviously it is only targeting the first cell. Can anyone help with how I can get it to iterate through column A as long as a cell in Column A has content?

Thanks for response.

I was hoping that this part:

LR = Range("A" & Rows.Count).End(xlUp).Row


would make it loop through the length of the contents of column A but it's not working.

Thanks for response.