Forum Discussion
How should I extract Yes and No in excel
Hi everyone
I have attach the excel sheet and wanted to find out how I could extract the 'x' inside the box to determine whether if the rows is "yes", "no" or "NA". I am completely lost with this question. My result would be using formula and compute the output as in column B. I have attached the excel file and need some help.
A worthwhile side note, illustrated by this exchange: within Excel there are always multiple routes from Point A to Point B, from problem to solution. It's one of the interesting and intriguing aspects of Excel's many functions.
This formula will work.
=IFERROR(IFS(FIND("x",A1)=2,"Yes",FIND("x",A1)=13,"No"),"NA")
Copy that down to all the necessary rows after first entering it in the top row.
All that does is find the position of the "x" in the text string, and then, based on that, give you the "YES" or "NO"
I'm puzzled by your column A, why that is what you start with. You needn't answer, and I recognize that maybe it's coming to you from outside and you're just trying to process it......but if it's something you've created, perhaps you could tell us the purpose. There might be other more effective ways to gather the data.
Picking up on the LET function as used by Patrick2788 , if you have a new enough version of Excel for LET to work, here's an adaptation of my approach using LET
=LET(P,FIND("x",A1),IFERROR(IFS(P=2,"Yes",P=13,"No"),"NA"))
FYI, if you're not familiar with LET, what it does is assign a value (in this case) to the letter P, assigned the value of the FIND function, so that it's not necessary to repeat the function. Then the rest of the formula works just as my prior version.
A 365 solution:
=LET(split,TEXTSPLIT(A1,{"[","]"}),CHOOSE(XMATCH("x",split),,"Yes",,"No"))
