Calc: find function error

In Calc if we have 2 cells setup like this:

A1: "A"
B1: =FIND(" ",A1)

B1 then shows: #VALUE!

I think I’m getting this error because a space is not found in the value in cell A1.

But how do I test for this error and/or bypass it? I want to do something if I find the space, and do something else if I don’t.

Here what I’ve already looked at/tried:

  • help says nothing about what to expect if the search string is not found.

  • tried checking it for -1

  • tried to use IFERROR and ISERROR functions on it. Perhaps I’m using them wrong??

  • B1: =SEARCH(" ",A1)

  • B1: =SEARCH("[:blank:]",A1)

  • B1: =FIND(" ",A1)

  • B1: =FIND("[:blank:]",A1)

=IF(ISERROR(FIND(" "; A1));"OneText"; "OtherText") e.g.
or, to get it the way the programming languages I know do it:
=IFERROR(FIND(" "; A1); 0) to get a 0 signaling “not found” and the position if the space occurs in A1 - and then to use this result in any way you want.

(Please also consider Why do I often get an error (508, 504, 502 e.g.) if I paste a Calc formula from some post into my sheet?)

(OT: My settings for V 6.3.3.2)

Thanks for the help!

I can also see that in your locale, the parameter separator is ; rather than , (comma). But I can see that if I put in a semicolon in (;) it automatically converts it to a comma here. Cool.

Thanks again!

Strange to me. In my formulas the semicolon persists.
I will attach a document containing my settings and a few explanations to my answer above as an OT element because attachments in comments are not allowed…

The effective function parameter separator displayed once the formula was entered depends on the settings.

Perhaps there is a better way, but I’ve just found that:

=ERRORTYPE(FIND(" ",A1)) returns 519.

link text explains the 519.

Better yet I think, is that ISERROR( FIND(" ", A1)) returns true, at which point you can then test ERRORTYPE(FIND(" ",A1)). I must have messed up when I tested it before.