Ask Your Question

Show a value if a text cell includes a specific string [closed]

asked 2021-01-10 20:19:04 +0100

David G L gravatar image

updated 2021-01-12 16:55:27 +0100

Processing a bank statement. Trivial problem.

I'm trying to show a copy of the 'In' amount in a different column if the corresponding description contains a specific string (such as 'Interest'). These can then give a column total of all 'Interest' rows.

I would expect IF(SEARCH("Interest", D6, 1) > 0, F6, "")

(for row 6 where the description is in column D and the 'In' payments are in column F) to work, but on the rows where 'Interest' is not found, the cell shows '#VALUE!'. I guess that the SEARCH function does not return a numeric value if a match isn't found, but the documentation doesn't say what is returned so I can't allow for it.

How to achieve this?

Been a programmer for nearly 50 years, but of course a spreadsheet function isn't a programming function.


Many thanks to Lupp and Earnest Al. ISNUMBER solves the problem.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by David G L
close date 2021-01-12 16:56:12.113873


Please consider to also read my answer to my own question here.

Lupp gravatar imageLupp ( 2021-01-10 21:32:47 +0100 )edit

Does question 282269 help?

Earnest Al gravatar imageEarnest Al ( 2021-01-10 21:36:25 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2021-01-10 21:29:23 +0100

Lupp gravatar image

updated 2021-01-11 00:45:59 +0100

As opposed to the usage of searching functions in general programming (also the InStr() function e.g. in LibreOffice Basic) the searching Calc functions don't return a 0 (zero) if no match was found, but an error.
You therefore need to use =IF(ISNUMBER(SEARCH("Interest"; D6)); F6;"") instead of what you tried. Unfortunately help texts seem to not expose this clearly.

(editing a bit later)
Just for fun. And because there are doubts concerning the reliability of any pulling to rubrics based on a few keywords. I always chose the rubric for any transaction manually in my bank-account data.
The little playground needs LibO V 6.2 or higher to work.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2021-01-10 20:19:04 +0100

Seen: 31 times

Last updated: Jan 12