How can I find a value from one cell in a char string in another?

[tiredazdaddy]

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$549=$H$3)*('Input
Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input Form'!R$4:R$549))),1,0))

 

[Glenn]

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$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$549))*('Input Form'!R$4:R$549<=$A126)*(NOT(ISBLANK('Input
Form'!R$4:R$549))),1,0))

 

[Shane Devenshire]

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$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$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$549=$H$3),--(R$4:R$549<=$A126),--(R$4:R$549<>""))


If this helps, please click the Yes button.

Cheers,
Shane Devenshire