Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find a value from one cell in a char string in another?
I am trying to figure out how to search for a specific alphanumeric value
from a cell in a character string in another cell. Does anyone know how to do this, or if it can be done via a Excel command? For instance, the below works fine if I only want to find an exact match for the values within column D for that in cell H3, but I do not know how to find the same value from H3 if the values in column D contain a match mixed in a character string. Any help would be greatly appreciated! =SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4:D$549=$H$3)*('Input Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find a value from one cell in a char string in another?
tiredazdaddy wrote:
I am trying to figure out how to search for a specific alphanumeric value from a cell in a character string in another cell. Does anyone know how to do this, or if it can be done via a Excel command? For instance, the below works fine if I only want to find an exact match for the values within column D for that in cell H3, but I do not know how to find the same value from H3 if the values in column D contain a match mixed in a character string. Any help would be greatly appreciated! =SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4:D$549=$H$3)*('Input Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0)) Use either FIND() or SEARCH(), depending upon whether you want it to be case-sensitive: =SUM(IF(('Input Form'!C$4:C$549=$G$3)*(FIND($H$3,'Input Form'!D$4:D$549))*('Input Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I find a value from one cell in a char string in another?
Hi,
A example of the data you are trying to compare would be most helpful. For example if all you want to know is if abc is in the string xabcr there might be one solution. If you want to know if abc is in the string axbycz there would be another solution. If you wanted find if AbC was in a string verses abc or ABC then yet another solution. Second, the formula you gave us test four ranges but gives no idea what range you want are talking about with regard to the above. however, for discussion purpose you can simplifiy =SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4:D$549=$H$3)*('Input Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0)) to =SUMPRODUCT(--(C$4:C$549=$G$3),--(D$4:D$549=$H$3),--(R$4:R$549<=$A126),--(R$4:R$549<"")) All I have done is remove the external reference for simplification and made other changes to the formula. This might work depending on your answers to the above questions: =SUMPRODUCT(--(ISNUMBER(FIND($G$3,C$4:C$549))) ,--(D$4:D$549=$H$3),--(R$4:R$549<=$A126),--(R$4:R$549<"")) If this helps, please click the Yes button. Cheers, Shane Devenshire "tiredazdaddy" wrote: I am trying to figure out how to search for a specific alphanumeric value from a cell in a character string in another cell. Does anyone know how to do this, or if it can be done via a Excel command? For instance, the below works fine if I only want to find an exact match for the values within column D for that in cell H3, but I do not know how to find the same value from H3 if the values in column D contain a match mixed in a character string. Any help would be greatly appreciated! =SUM(IF(('Input Form'!C$4:C$549=$G$3)*('Input Form'!D$4:D$549=$H$3)*('Input Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND 1 char in cell of any 3 char =True | Excel Discussion (Misc queries) | |||
How to retrieve certain char within a string? | Excel Discussion (Misc queries) | |||
How to verify the first char of the string in excel? | Excel Discussion (Misc queries) | |||
How do i get the last position of a char (space) in a string? | Excel Worksheet Functions | |||
string/char CONTAINS function? | Excel Worksheet Functions |