Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Find first character in cell

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Find first character in cell

Try this in A2

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

Mike

"mahmad" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default Find first character in cell

Hi,
Try this in another cell:
=IF(ISERR(FIND("A",A1)),"NO","YES")
Regards - Dave.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Find first character in cell

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

On Oct 2, 12:31*pm, mahmad wrote:
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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Find first character in cell

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


"Pete_UK" wrote:

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

On Oct 2, 12:31 pm, mahmad wrote:
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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Find first character in cell

=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" wrote:

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

On Oct 2, 12:31 pm, mahmad wrote:
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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Find first character in cell

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

On Oct 2, 1:51*pm, mahmad wrote:
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



"Pete_UK" wrote:
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


On Oct 2, 12:31 pm, mahmad wrote:
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- Hide quoted text -


- Show quoted text -


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Find first character in cell

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

Pete

On Oct 2, 1:59*pm, Teethless mama
wrote:
=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" wrote:
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


On Oct 2, 12:31 pm, mahmad wrote:
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- Hide quoted text -


- Show quoted text -


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default Find first character in cell

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)


"Pete_UK" wrote in message
...
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

On Oct 2, 1:51 pm, mahmad wrote:
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



"Pete_UK" wrote:
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


On Oct 2, 12:31 pm, mahmad wrote:
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- Hide quoted text -


- Show quoted text -


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Find first character in cell

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" wrote:

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)


"Pete_UK" wrote in message
...
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

On Oct 2, 1:51 pm, mahmad wrote:
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



"Pete_UK" wrote:
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


On Oct 2, 12:31 pm, mahmad wrote:
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- Hide quoted text -


- Show quoted text -





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default Find first character in cell

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?

--
Rick (MVP - Excel)


"mahmad" wrote in message
...
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" wrote:

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)


"Pete_UK" wrote in message
...
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

On Oct 2, 1:51 pm, mahmad wrote:
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



"Pete_UK" wrote:
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

On Oct 2, 12:31 pm, mahmad wrote:
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- Hide quoted text -

- Show quoted text -




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find replace the 1st character in a cell Pete Excel Discussion (Misc queries) 2 March 31st 08 07:27 AM
find a cell with a character Dan Kowalski Excel Worksheet Functions 1 February 28th 08 06:00 PM
how to find number in a cell , a cell contains character data ornumeric data [email protected] Excel Worksheet Functions 3 February 19th 08 08:29 PM
Find max number of character and return cell address ExcelMonkey Excel Worksheet Functions 5 April 15th 06 04:13 AM
Find a character to sort on in an Excel cell if not the 1st charac drrons Excel Worksheet Functions 1 November 12th 05 12:43 AM


All times are GMT +1. The time now is 06:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"