Forum Discussion
HOW TO: "If cell contains specific text display the immediate next word after it"
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"?re:not quite the thing but sooo close!
Please Show your expected result based on the original data source
select 1,* from Sheet1 union
select 2,colIdxf[0:]{regexp2('(?<=-\s)\w+',%s) %s} from Sheet1;
https://support.microsoft.com/en-us/office/regexextract-function-4b96c140-9205-4b6e-9fbe-6aa9e783ff57#:~:text=The%20REGEXEXTRACT%20function%20allows%20you%20to%20extract%20text,matches%20or%20capturing%20groups%20from%20the%20first%20match.
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.
