HOW TO: "If cell contains specific text display the immediate next word after it"

Copper Contributor

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.

7 Replies

That could be

=MID(M26,FIND("-",M26)+2,FIND("-",M26,FIND("-",M26)+1)-FIND("-",M26)-2)

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")
sorry i am literally new to this site, i attached the file to the original post just now

Because of my mistake the formula you provided did not match, sorry :\
for some reason this returns the word after the "-" and the first word on the next line of the same cell, i included a copy of the original file in my original comment now to avoid confusing everyone

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

 

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"?

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.