INSTR Function not working 2

[malaygal]

I am quite stuck with this.
I have been using the INSTR FUNCTION on all of my Excel projects/workbooks and it's been working properly.
But now, it does not seem to work. I have a text column (A1) and I want to evaluate the presence of certain string.

=INSTR(A1,"WIN") should give me integer for WINDOWS and BERWIN.

I get "#NAME?" on both

I tried FIND, it worked when string is found but gives "#VALUE!" if not found.

Any help will be greatly appreciated.

 

[AnotherHiggins]

I'm afraid that you've mistaken. INSTR is not an Excel worksheet function.

You have probably been using either SEARCH or FIND.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[malaygal]

I believe you are right, I might have been using INSTR in my VBA code. But maybe I could continue with this post for help with the FIND function.

I am putting a formula on a column that will compare current row to the next row. I have gone as far as this

=if(FIND("WIN",A1) <> 0,IF(FIND("WIN",A2)<> 0,1,2),3)

I have three possible values (1,2,3).
1 - If Next row contains the string "WIN"
2 - If Next row does not conatain the string "WIN"
3 - if current row does not contain the string "WIN"

Again, if the string WIN is not found, I get "#VALUE!" for cells that eavluates to 2 or 3.

 

[GlennUK]

You'll need to use the ISERROR function to test your results:

=if(ISERROR(FIND("WIN",A1)),3,IF(ISERROR(FIND("WIN",A2)),2,1))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[malaygal]

Thank you both for the quick and helpful response.

 

[NWBeaver]

You are correct about using Instr with VBA, it is a VB string function which can be used in either a VB script or or a worksheet code module.

i = InStr(1, objCell.Value, "win", vbTextCompare)