Simplify text from range of text options

**lindaroslin** · 09-13-2012, 01:54 PM

A cell contains either "Yes", "Yes +Comment", "No", "No + Comment", "Comment only", or "No response"
I want to summarise these, so that the neighbouring cell will contain Yes, No, or No response ie.

"Yes" or "Yes +Comment" = "Yes"
"No" or "No + Comment" = "No"
"Comment only" or "No response" = "No response".

Help please, Linda

**robertswart** · 09-13-2012, 02:02 PM

Let's say cell A2 contains the yes or no or one of them with comment, then put in cell B2:

Formula:

Please Login or Register  to view this content.

This assumes only yes or no are the first word in the cell, no answers like maybe or don't know.

**jeffreybrown** · 09-13-2012, 02:05 PM

Hi Linda & Welcome to the Forum,

You could build a lookup table and then use

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=VLOOKUP(A1,H1:I6,2,0)

column H is the comment and column I contains the response.

A1 is the comment you are looking up

**Ace_XL** · 09-13-2012, 02:07 PM

Use..

=IF(ISNUMBER(SEARCH("Yes",A1)),"yes",IF(OR(ISNUMBER(SEARCH("No response",A1)),NOT(ISNUMBER(SEARCH("No",A1))),"No response"),"No"))

**lindaroslin** · 09-13-2012, 06:02 PM

Thanks for trying, but I'm sorry, that's not working - it only works for Yes and Yes + comment, not for No, No + Comment, Comment only or No response.
Linda

**FDibbins** · 09-13-2012, 07:15 PM

try this...
=IF(LEFT(A6,3)="Yes","Yes",IF(LEFT(A6,2)="No","No",IF(OR(LEFT(A4,3)<>"Yes",LEFT(A4,2)<>"no",A4=""),"No response","")))

**lindaroslin** · 09-13-2012, 10:13 PM

Originally Posted by FDibbins

try this...
=IF(LEFT(A6,3)="Yes","Yes",IF(LEFT(A6,2)="No","No",IF(OR(LEFT(A4,3)<>"Yes",LEFT(A4,2)<>"no",A4=""),"No response","")))

Solved
Many thanks FDibbins

, that works a treat when I substitute the same reference cell for A6 and A4.
I'll now sit quietly and try to understand why it works.
Linda

**jeffreybrown** · 09-13-2012, 10:24 PM

Glad to hear you have a workable solution Linda and thanks for the feedback

A good way to break down a formula is by using evaluate formula

Place cursor on the formula >> On the ribbon >> Formulas >> Formula Auditing >> Evaluate Formula

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**FDibbins** · 09-14-2012, 12:33 PM

glad to help, when you mentioned A4, i saw that it was supposed to be A6, sorry about that, and good catch from your side.
what its doing is testing the 1st 2 or 3 letters for yes or no...and then neither yes or no BUT also has No input. hope that helps?
thx for the star, too