Extracting Name from column [Closed]

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

1 reply


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!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!