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

Ask Your Question

Calc: find function error

asked 2019-12-20 00:08:30 +0200

EasyTrieve gravatar image

updated 2019-12-20 00:12:42 +0200

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

2 Answers

Sort by » oldest newest most voted

answered 2019-12-20 00:22:02 +0200

Lupp gravatar image

updated 2019-12-20 09:57:36 +0200

=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 https://ask.libreoffice.org/en/questi....)

(OT: My settings for V

edit flag offensive delete link 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!

EasyTrieve gravatar imageEasyTrieve ( 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..

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

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

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

answered 2019-12-20 00:17:01 +0200

EasyTrieve gravatar image

updated 2019-12-20 01:20:12 +0200

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-12-20 00:08:30 +0200

Seen: 450 times

Last updated: Dec 20 '19