FIND does not find text

[General Ledger]

FIND is driving me crazy!!! I have a calculation in a column =FIND("~~",[Note Text]) which should return that it found the text.

However, I get #ERROR: Calculation error in column 'Patient Notes Body'[]: The search Text provided to function 'FIND' could not be found in the given text.

I confirmed the field being examined (Note Text) is text by using ISTEXT function.

I tried using different text to FIND that I know is in Note Text and continue to get the error.

Is the FIND function very fussy and moody?

Thanks,

G/L

 

[scottsen]

I really suspect you just have SOME rows that don't match ~~. Wrap it in IFERROR

=IFERROR(FIND("~~",[Note Text]), -1)

 

[General Ledger]

I am confident all rows have the text to be found (~~). Even if some rows might not, why would every row return an Error? I think the problem is something more than can be solved by IFERROR but will try it anyways.

Thanks for the tip.

 

[mozartiano]

Hello.

Well, when I was trying to build the following sintax I got the same problem with the FIND command:

=SUBSTITUTE([Despesa_];[Despesa_];TRIM(MID([Despesa_];(IFERROR(SEARCH("-";[Despesa_]);0)+1);LEN([Despesa_])-IFERROR(SEARCH("-";[Despesa_]);0))))

Then I changed to the SEARCH and the error was still there.

However, in Microsoft's page for the SEARCH function there is the explanation:

(SEARCH Function)
[h=3]Description[/h]The formula in the preceding example will fail if the search string is not found in every row of the source column. Therefore, the next example demonstrates how to use IFERROR with the SEARCH function, to ensure that a valid result is returned for every row.
The following formula finds the position of the character "-" within the column, and returns -1 if the string is not found.

[h=3]Code[/h]



= IFERROR(SEARCH("-",[PostalCode]),-1)</pre>




[h=3]Comments[/h]Note that the data type of the value that you use as an error output must match the data type of the non-error output type. In this case, you provide a numeric value to be output in case of an error because SEARCH returns an integer value.
However, you could also return a blank (empty string) by using BLANK() as the second argument to IFERROR.


So, we are obliged to use the IFERROR if any of the cells does not match the SEARCH function.

IFERROR(SEARCH("~~";[field you are searching]);-1)