Forum Discussion
HOW TO: "If cell contains specific text display the immediate next word after it"
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.
12 Replies
Hello, i don't know if im in a right thread since im new here and dont know how to post.
My problem is thisI want my result in column B will be like this it extracted the before the dash but if in column A dont have "-" it will return its original word
select *,regreplace('-.*','',Input) from Sheet1;
https://support.microsoft.com/en-us/office/regexreplace-function-9c030bb2-5e47-4efc-bad5-4582d7100897
Regarding the thread (discussion), I would say "If in doubt, start a new thread (discussion). You can always include a link in your post that refers to the closely-related thread(s), if that is appropriate."
You don't say which Excel product you are using (Excel 365, Excel 2019, etc.). If you are using Excel 365 or Excel for the web, the new TEXTBEFORE function handles this nicely:
=TEXTBEFORE(" - ", A1)(Most spaces in my formulas are optional; I include them for improved readability. But in this case the spaces around the hyphen are appropriate.) Change the A1 reference if appropriate, and copy the formula down as needed.
If you are using an older Excel product, this see the attached workbook.
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 :/
That could be
=MID(M26,FIND("-",M26)+2,FIND("-",M26,FIND("-",M26)+1)-FIND("-",M26)-2)- 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
