how to nest two if functions

[hxkresl]

I'm building a formula, that will evaluate whether a cell contains a tilde, and then return the position of the tilde.

First, I identify if the cell contains a tilde. This works:
=IF(IF(ISERROR(FIND("~",B2)),"Not found", "Found")

Next, I'd like the position of the tilde returned to the cell.
SEARCH(b2,"~",1)

This is what I came up with, but getting #VALUE!:
=IF(IF(ISERROR(FIND("~",B2)),"Not found", "Found")="Found",SEARCH(B2,"~",1),"")

 

[hiker95]

hxkresl,

How about:

Excel 2007
	B	C
2	Find the ~ please.	Found at 10
3	Find the ? please.	Not found
4
Sheet1

Cell Formulas
Range	Formula
C2	=IF(ISERROR(FIND("~",B2,1)),"Not found","Found at " & FIND("~",B2,1))
C3	=IF(ISERROR(FIND("~",B3,1)),"Not found","Found at " & FIND("~",B3,1))

 

[hxkresl]

Yes, works well. Thank you.

 

[hiker95]

hxkresl,

Thanks for the feedback.

You are very welcome. Glad I could help.

Please come back anytime.

 

[Peter_SSs]

If you happen to be using Excel 2007 or later, you could use the IFERROR function:

=IFERROR("Found at "&FIND("~",B2),"Not found")

 

[hxkresl]

If you happen to be using Excel 2007 or later, you could use the IFERROR function:

=IFERROR("Found at "&FIND("~",B2),"Not found")

That's very nice too. I have to build on this however, to evaluate the length of the text minus the ~, and it's going in the following direction:
=IF(ISERROR(FIND("~",B2,1)),LEN(B2),LEN(B2)-1)
so I'm going to go with first one.

Thanks,
hxkresl

 

[Peter_SSs]

=IF(ISERROR(FIND("~",B2,1)),LEN(B2),LEN(B2)-1)

I think you'll find this does the same thing without having to worry about errors at all.

=LEN(B2)-(FIND("~",B2&"~")<=LEN(B2))