Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find first character in cell
Hi,
Try this in another cell: =IF(ISERR(FIND("A",A1)),"NO","YES") Regards - Dave. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find replace the 1st character in a cell | Excel Discussion (Misc queries) | |||
find a cell with a character | Excel Worksheet Functions | |||
how to find number in a cell , a cell contains character data ornumeric data | Excel Worksheet Functions | |||
Find max number of character and return cell address | Excel Worksheet Functions | |||
Find a character to sort on in an Excel cell if not the 1st charac | Excel Worksheet Functions |