OK, you can use three nested functions for this one, are you ready?
First one to see is column, if you enter =column() into a cell, it will return a numerical value for the column in which the cell has the function in.
Now, wrap that up into the ADDRESS Function, this is a little bit more tricky:
ADDRESS(row_num, column_num, [abs_num]).
In this case, [abs_num] = 4 , which is the designator of relative address for this function. It can be ommitted or one which is absolute, 2 is Absolute row; relative column, 3 is Relative row; absolute column, and 4 is relative. So that addrewss portion of the formula looks like this:ADDRESS(1,COLUMN(),4)
Now, we break down the substitute():
old_text=becuase of th eway the address returns, it will have a built in "1" with it, the substitute removes the one, wnd replaces it with nothing ("").