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
Blocked Profile - Jan 23, 2014 at 10:51 AM
Related:
- Extracting Name from column
- How to delete column in word - Guide
- Tweetdeck remove column - Guide
- How to extract contacts from sim card - Guide
- Extract audio from messenger - Guide
- Extract audio from video vlc - Guide
1 response
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!
=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!
