Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
I need to find a specific number from a list of numbers within a range of
cells. For example: Column B 1 2 3 4 5 10,11 9,8 12 11 I need to find if 11 is in the list. Find if shows each cell with an "11" but I can't get a function to work. I tried =countif(b$2:b$16,"11")0 but it doesn't work. I can treat the cells as a number or text, whichever will make the function work. Basically, I am trying to fiind if a value exists in a column and reutrn a yes/no to create an automatic proof reading tool. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
=MATCH(11,A1:A100,0)
return row number "Lost in Microbiology" skrev: I need to find a specific number from a list of numbers within a range of cells. For example: Column B 1 2 3 4 5 10,11 9,8 12 11 I need to find if 11 is in the list. Find if shows each cell with an "11" but I can't get a function to work. I tried =countif(b$2:b$16,"11")0 but it doesn't work. I can treat the cells as a number or text, whichever will make the function work. Basically, I am trying to fiind if a value exists in a column and reutrn a yes/no to create an automatic proof reading tool. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
This only works if there is a single value. Any cell with a comma separated
field returns #N/A. Even if I could find a conditional format that would highlight the cell that contains the number. "excelent" wrote: =MATCH(11,A1:A100,0) return row number "Lost in Microbiology" skrev: I need to find a specific number from a list of numbers within a range of cells. For example: Column B 1 2 3 4 5 10,11 9,8 12 11 I need to find if 11 is in the list. Find if shows each cell with an "11" but I can't get a function to work. I tried =countif(b$2:b$16,"11")0 but it doesn't work. I can treat the cells as a number or text, whichever will make the function work. Basically, I am trying to fiind if a value exists in a column and reutrn a yes/no to create an automatic proof reading tool. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
Hi Lost,
As there is text and numbers this should work =COUNTIF(B$2:B$16,"*11*")+COUNTIF(B$2:B$16,11) HTH Martin |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
Woops that should be
=IF(COUNTIF(B$2:B$16,"*11*")+COUNTIF(B$2:B$16,11) 0,"Yes","No") HTH Martin |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
Try this:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH("11",A1:A9)))0,"found","not found") "Lost in Microbiology" wrote: I need to find a specific number from a list of numbers within a range of cells. For example: Column B 1 2 3 4 5 10,11 9,8 12 11 I need to find if 11 is in the list. Find if shows each cell with an "11" but I can't get a function to work. I tried =countif(b$2:b$16,"11")0 but it doesn't work. I can treat the cells as a number or text, whichever will make the function work. Basically, I am trying to fiind if a value exists in a column and reutrn a yes/no to create an automatic proof reading tool. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
Thanks Martin, that seems to do the trick!
Until someone asks me to do this in a slightly different way, this looks like the answer! "MartinW" wrote: Woops that should be =IF(COUNTIF(B$2:B$16,"*11*")+COUNTIF(B$2:B$16,11) 0,"Yes","No") HTH Martin |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
This was working, but now I am finding it will say "Yes" for a 1 if there is
a 11,12,13,etc. in the column. Is there a way around this? Should I change the column to text? Thanks "MartinW" wrote: Woops that should be =IF(COUNTIF(B$2:B$16,"*11*")+COUNTIF(B$2:B$16,11) 0,"Yes","No") HTH Martin |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find specific value in a string
I can't get it to return a "Yes" for anything but 11 in my trials.
How is your data setup where you get "Yes" for a 1. I assume you have altered the formula to match your actual data rather than the example you posted. Please post a copy of the new formula along with an explanation of the nature of your data. Regards Martin "Lost in Microbiology" wrote in message ... This was working, but now I am finding it will say "Yes" for a 1 if there is a 11,12,13,etc. in the column. Is there a way around this? Should I change the column to text? Thanks "MartinW" wrote: Woops that should be =IF(COUNTIF(B$2:B$16,"*11*")+COUNTIF(B$2:B$16,11) 0,"Yes","No") HTH Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Find Many String options in ONE String | Excel Worksheet Functions | |||
Finding specific text in a string | Excel Worksheet Functions | |||
Text String - Specific Characters | Excel Worksheet Functions | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) |