How to pull "A" from a cell with "AB"

[DokHoliday]

Hello everyone, first time posting here. I'm trying to retrieve information from a table like the one below as such:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C(function)[/TD]
[TD]C(outcome)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]Harry[/TD]
[TD]=IF(A1="a","YES","NO")[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ab[/TD]
[TD]Ron[/TD]
[TD]=IF(A2="a","YES","NO")[/TD]
[TD]NO[/TD]
[/TR]
</tbody>[/TABLE]

Basically if a cell in column A contains "a" I want excel to output "YES" in Column C. This works for Harry but since Ron has entered "ab" it can't, using my formula, decipher that "ab" does contain an "a".

I found this formula in another thread: =IF(ISNUMBER(FIND("a",A2)),"YES","NO") and it works! Only I don't understand how it's working, for example the ISNUMBER and FIND function. What about using ISTEXT? What's the difference between FIND and SEARCH?

Help.

Thanks in advance.

 

[jim may]

Does this work for you?

Excel 2012

	A	B	C

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFFFF"]a[/TD]
[TD="bgcolor: #FFFFFF"]Harry[/TD]
[TD="bgcolor: #FFFFFF"]YES[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]ab[/TD]
[TD="bgcolor: #FFFFFF"]Ron[/TD]
[TD="bgcolor: #FFFFFF"]NO[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]=IF(AND(LEFT(A1,1)="a",LEN(A1)=1),"YES","NO")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(AND(LEFT(A2,1)="a",LEN(A2)=1),"YES","NO")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DokHoliday]

Not quite. I need it to return a value of "YES" for Ron because his response (A2) contains an "a". However because it doesn't only just contain an "a" excel is marking it as no.

 

[Jonmo1]

There are only a few differences between search and find.
The most relevant one to this thread is that FIND is Case Sensitive, SEARCH is not.

So if you had "And" in A1
=SEARCH("a",A1) would find it
=FIND("a",A1) would NOT.

The use of ISNUMBER is because FIND will return the position # in the string where the searched value is found.
So if you searched for "a" in "whatever", it would return 3, a is in the 3rd position.
But if the value is NOT found, then FIND returns the #VALUE! error.

ISNUMBER tests if the result of the FIND is a number or not.
If ISNUMBER is TRUE, then the FIND did find what it was looking for, FALSE then it did NOT.


Hope that helps.

 

[DokHoliday]

There are only a few differences between search and find.
The most relevant one to this thread is that FIND is Case Sensitive, SEARCH is not.

So if you had "And" in A1
=SEARCH("a",A1) would find it
=FIND("a",A1) would NOT.

The use of ISNUMBER is because FIND will return the position # in the string where the searched value is found.
So if you searched for "a" in "whatever", it would return 3, a is in the 3rd position.
But if the value is NOT found, then FIND returns the #VALUE! error.

ISNUMBER tests if the result of the FIND is a number or not.
If ISNUMBER is TRUE, then the FIND did find what it was looking for, FALSE then it did NOT.


Hope that helps.

You, my friend, are a genius. That function makes complete sense now! It's interesting that they don't have a function that will find letters contained in a cell but this works perfectly. Thanks for the help, everyone!

 

[Jonmo1]

It's interesting that they don't have a function that will find letters contained in a cell

I'm confused.
They absolutely DO have functions that will find letters in a cell.
They're called SEARCH and FIND.

That's what this whole thread was about.

??

 

[DokHoliday]

I'm confused.
They absolutely DO have functions that will find letters in a cell.
They're called SEARCH and FIND.

That's what this whole thread was about.

??

I suppose you're right. I guess I would expect the FIND("a",A3) to return a TRUE or FALSE on it's own and not the number in the string where it's located.

Thanks again!

 

[Jonmo1]

You're welcome.

It's good that it returns a number.
More often than Not, these functions are used to split strings based on the location of substrings..

If you have say
"George Washington"
And you want to split that based finding the space character, then you need more than just a true/false result when testing if the string contains a space.
You need to know 'where' the space exists within the string.