Find first character in cell

[mahmad]

Hi,

I want to be able to find if a particular cell which contains a specific
letter and if it does then say yes in another cell. I am using Excel 03.

for example.

cell a1 = ABC (i am interesed in letter A) as this cell includes letter i
want a YES in cell a2.

thanks

Mo

 

[Mike H]

Try this in A2

=IF(LEFT(A1,1)="A","Yes","")

Mike

 

[Dave]

Hi,
Try this in another cell:
=IF(ISERR(FIND("A",A1)),"NO","YES")
Regards - Dave.

 

[Pete_UK]

Try this:

=IF(UPPER(LEFT(A1,1))="A","yes","no")

to check for the first character, or this:

=IF(ISNUMBER(SEARCH("A",A1)),"yes","no")

to check anywhere in A1. Of course, the "A" could be replaced by a
cell reference which contains the letter you are interested in. Both
of these are case-insensitive.

Hope this helps.

Pete

 

[mahmad]

thanks for your help guys, I want to expand the idea a little further.

If cell A1 = ABCD

I would like to output in another cell to something different depending on
the letter.

e.g.

A1 = ABCD if A or B or C then blue


thanks

 

[Teethless mama]

=IF(UPPER(LEFT(A1,1))="A","yes","no")
LEFT function is not a case sensitive. You no need a UPPER function


=IF(LEFT(A1)="A","yes","no")

 

[Pete_UK]

Variation of my second formula:

IF(OR(ISNUMBER(SEARCH("A",A1)),ISNUMBER(SEARCH("B",A1)),
ISNUMBER(SEARCH("C",A1))),"blue","")

If you meant that you want to colour the cell blue, then you would
need to do this with Conditional Formatting.

Hope this helps.

Pete

 

[Pete_UK]

Ah yes, thanks for pointing that out, TM (although it would still work
with it).

Pete

 

[Rick Rothstein]

Your formula will return "blue" if A, B or C exists anywhere in A1... I
think the OP wanted to return "blue" only if the first character was an A, B
or C. This formula should do that...

=IF(ISNUMBER(FIND(LEFT(A1),"ABC")),"blue","")

--
Rick (MVP - Excel)


Variation of my second formula:

IF(OR(ISNUMBER(SEARCH("A",A1)),ISNUMBER(SEARCH("B",A1)),
ISNUMBER(SEARCH("C",A1))),"blue","")

If you meant that you want to colour the cell blue, then you would
need to do this with Conditional Formatting.

Hope this helps.

Pete

 

[mahmad]

Hi rick,

I want to return a value in another cell if the value in cell A1 is either A
or B or C etc, and not necesalry be the first letter in the cell. what i am
trying to do is as follow.

allocate customer with the follwoing characters to a particular sales person.

cell A1
ABCD if A then John
ABCD if B then sam
ABCD if C then jason

and so on

thanks

Mo

 

[Rick Rothstein]

Your example is confusing. You say the letter does not have to be the first
letter in A1, then you show ABCD in A1 and test show tests for A, B, C
individually and give 3 different answers for the **same** value in A1. Can
you clarify this a little bit? How exactly do you expect your tests to work?
What output are you looking for?