We will be migrating from Ask to Discourse on the first week of August, read the details here

# 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:

• 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)
edit retag close merge delete

Sort by » oldest newest most voted

=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.

(OT: My settings for V 6.3.3.2)

more

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!

( 2019-12-20 01:25:23 +0200 )edit

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..

( 2019-12-20 09:56:19 +0200 )edit

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

( 2019-12-20 15:48:41 +0200 )edit

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

=ERRORTYPE(FIND(" ",A1)) returns 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.