Sep 27 2018 12:51 PM - edited Sep 27 2018 01:42 PM
I have an excel file with each individual cell filled with data as it follows:
Alejandro - GREEN
Daniel - RED
Sebastian - BLUE
What I have been trying to do is to use a formula to extract the upper case value thats after the "-" immediate to the specific name of the person.
At the moment I have the formula:
=IF(COUNTIF(F3:F20,"*"&"Daniel"&"*"),"Yes","No")
Which would simply return "Yes" if the cell contains the name Daniel, what I dont know how to do is to replace the "Yes" for a formula that would give me the "RED" in return.
I know the formulas Left, Mid and Right are probably the way to go but since the separating character appears 3 times in a single cell (it being "-" I THINK) I have no idea how to stablish to use only the one after the specific name, and return only the very first word next to it (RED in case of Daniel).
For this I have the formula
=MID(M26,FIND("-",M26)+2,3)
Which would give me GRE (3 characters only, dont know how to make it dynamic length) and would only return the very first entry as opposed to it being the one after the specific name, kinda close but no luck yet :\
Is this even possible?
Edit: I attach my original file, I didnt before because the formula get so convoluted with other stuff I tried to keep it simple, basically its the formula on column O where i would replace the ":D" with the formula that im asking about.
Sep 27 2018 01:28 PM
That could be
=MID(M26,FIND("-",M26)+2,FIND("-",M26,FIND("-",M26)+1)-FIND("-",M26)-2)
Sep 27 2018 01:33 PM
Alejandro, you leave some room for interpretation because you did not provide a sample file with the desired results.
My guess woulds be:
=IF(COUNTIFS(A1,"*Daniel*"),SUBSTITUTE(A1,"Daniel - ",""),"No")
Sep 27 2018 01:44 PM
Sep 27 2018 01:46 PM
Sep 27 2018 02:07 PM
If only from first line (actually second in the cell since 27 is in the first line)
=MID(M26,FIND("-",M26)+2,FIND(CHAR(10),M26,FIND(CHAR(10),M26)+1)-FIND("-",M26)-2)
and attached
Sep 27 2018 02:20 PM - edited Sep 27 2018 02:22 PM
Sep 27 2018 02:20 PM - edited Sep 27 2018 02:22 PM
Edit: now that i notice it never asks which name it is substracting from :\ it got the first one instead, not quite the thing but sooo close!
One quick question, how would you make it dynamic? so that it uses the cell that matched the countifs conditional i have on column O instead of a hardcoded "M26"?
Sep 28 2018 06:02 AM
I still not sure I understood your logic and what you'd like to do, but in general you may find first the cell where the name with dash is
=IFNA(INDEX($J$26:$N$26,MATCH(1,INDEX(--ISNUMBER(SEARCH(F3&" -",$J$26:$N$26)),1,0),0)),"")
and extract second part from returned value
=IFERROR(MID(Q3,FIND("-",Q3)+2,FIND(CHAR(10),Q3,FIND(CHAR(10),Q3)+1)-FIND("-",Q3)-2), "N/A")
Please see in Q3, R3. Sure above formulas could be combined in one.