search a row for text

[nayone69]

Hi there,

what seems quite simple is giving me a headache>

What I an trying to do is with a formula search a row with multiple cells with various narrative to see if it contains the text 'fx' or 'FX' and just need yes no response
any pointers other than VBA

Alex

 

[VoG]

Try like this

=IF(ISNUMBER(MATCH("fx",A1:D1,0)),"Yes","No")

 

[SteveO59L]

Something along the lines of

=FIND("c",(CONCATENATE(A6,B6,C6,D6,E6,F6)))

 

[Aladin Akyurek]

Hi there,

what seems quite simple is giving me a headache>

What I an trying to do is with a formula search a row with multiple cells with various narrative to see if it contains the text 'fx' or 'FX' and just need yes no response
any pointers other than VBA

Alex

=LOOKUP(9.99999999999999E+307,SEARCH(E2,A2:A10),A2:A10)

would return the contents of the A-cell where E2 housing something like fx (case insensitive) occurs.

 

[nayone69]

thx for all the suggestions
=IF(ISNUMBER(MATCH("fx",A1:D1,0)),"Yes","No")

only works if the FX or FX is not within text in a cell

=FIND("fx",(CONCATENATE(A6,B6,C6,D6,E6,F6)))

does surprisingly well

and
=LOOKUP(9.99999999999999E+307,SEARCH(E2,A2:A10),A2:A10)
works but I have no idea how, very clever thx

Alex

 

[VoG]

Maybe then

=IF(ISNUMBER(MATCH("*fx*",A1:D1,0)),"Yes","No")

 

[Aladin Akyurek]

thx for all the suggestions
=IF(ISNUMBER(MATCH("fx",A1:D1,0)),"Yes","No")

only works if the FX or FX is not within text in a cell

Requires wild-carding. See Peter's follow up.

=FIND("fx",(CONCATENATE(A6,B6,C6,D6,E6,F6)))

does surprisingly well

Yes, but it's case sensitive in case that's relevant.

and
=LOOKUP(9.99999999999999E+307,SEARCH(E2,A2:A10),A2:A10)
works but I have no idea how, very clever thx

Alex

SEARCH and FIND behaves somewhat similar.

SEARCH returns an array of integers and/or #VALUE! errors. LOOKUP with the big number as look up value picks out the row number of the last integer and returns the value at that row number.

See post #7 in:

http://www.mrexcel.com/forum/showthread.php?t=310278

for an explanation of the mechanics.

 

[T. Valko]

Hi there,

what seems quite simple is giving me a headache>

What I an trying to do is with a formula search a row with multiple cells with various narrative to see if it contains the text 'fx' or 'FX' and just need yes no response
any pointers other than VBA

Alex

Here's another one:

=IF(COUNTIF(A1:E1,"*FX*"),"Yes","No")