Extracting Name from column

Closed
esmasas Posts 1 Registration date Thursday January 23, 2014 Status Member Last seen January 23, 2014 - Jan 23, 2014 at 09:35 AM
 Blocked Profile - Jan 23, 2014 at 10:51 AM
I would just like to extract the name from a column: 224. <----------email removed by moderator-------> Can someone help me do that?

1 reply

Blocked Profile
Jan 23, 2014 at 10:51 AM
OK, you can use three nested functions for this one, are you ready?

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

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():
SUBSTITUTE(text,old_text,new_text,instance_num)
text=ADDRESS(1,COLUMN(),4)
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 ("").

Have Fun!
0