In A1 I have =FIND(B1,“R”). In B1 I have “RPC”. Why does A1 show #VALUE! ?
In A2 I have “RPC”. In B2 I have =find(a2,“R”). It doesn’t even treat it as a formula, B2 just displays the text " =find(a2,“R”) "
What’s going on?
I tried that no luck. Another oddity is that with some target text cells, I couldn’t get a value() output unless I test for it as below.
=VALUE(M164) wouldn’t work, but
=IF(M164<>"",VALUE(M164),"") did.
And then I edited the second formula above to match the first on, and it worked.
This is pretty bad. I lost way too much time fiddling. I just want to use my tech not fiddle under the hood. Good thing most of the time it works.
Try with Ctrl+F8. Text will show in black, numbers in blue, and formulas in green.
Also, you can edit your question, and share a reduced sample file to test.
Thanks for that helpful tip.
menu:Tools>Options>Calc>Formula>“Use English function names” and =FIND(“R”;B1) will work.
Didn’t know about having to tell it what language to use. I’ll keep that in mind for next time. That formula has worked in the (distant) past. I didn’t have time to wait so I figured a workaround. Thanks.
You do not tell us the faintest clue about your office installation, therefore we can not know which formula language is the right one. It would be the language of your user interface. The sidebar and the formula wizard include a catalog of all functions.
When the upper case name “FIND” is turned into lower case name “find”, this indicates that this name is not recognized. Either you switch the entire user interface language to English or you tell the Calc component to use English function names or you use the function name that is defined for your UI langauge.
1 Like
Never had to tell an app what language to use. Didn’t know that the formulas were case sensitive thanks. I’ll keep that in mind.
No, formulas are case-insensitive. However, if entering =FIND(…) gives an error value #NAME! with the formula altered to =find(…) then this clearly indicates that your user interface is not English (which is what you still don’t tell us).
Calc changes the not recognized tokens to lower case. For instance, in a German user interface I have to enter =FINDEN(“B”;A1). If I enter =FIND(“B”;A1) I get the #NAME! error and the formula reads =find(“B”;A1). Now I can do one of the following:
- Enter the correct function name for my user interface language.
- Change the user interface language to English.
- Keep the German UI language and tell the program that I prefer English function names anyway.
OK, OK. You got the #VALUE error which indicates something else. Upload your document to spare us wild guessing.
t90401.ods (14.0 KB)
as you can see, FIND is case sensitive just as documented. The formula entry is case-insensitive.
-
I’m on windows 10 pro. LO version is:
Version: 7.4.6.2 (x64) / LibreOffice Community
Build ID: 5b1f5509c2decdade7fda905e3e1429a67acd63d
CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL -
I went to Tools/Options/Language Settings/Languages and the User interface is "Default - English (USA). I think remember seeing a reference to language specifically as I was entering formulas, but I can’t find it now.
-
It’s downloaded financial data that I can’t give out. I’ve gotten around the problem for the work I need to do with the file.
-
Since FIND has worked before, I’m dealing with this in between other things to try to learn. I appreciate the persistence of all of you.
-
Just now I put the target text “R” in first position, before the target cell reference – the opposite of my examples above. This worked. Now I feel I can try to use it, as I have in the past, to extract substrings from the target cell.
Thank you all again.
Just visit the Help link given in Find syntax...or something else? - #2 by LeroyG and see the order of parameters FindText and Text that have never changed.
FIND(“R”;A1) returning #VALUE simply means that the string “R” (case-sensitive) can not be found in A1. Unfortunately, this is undocumented. It is a 30-year-old spreadsheet traditional.
SEARCH(“R”;A1) would be the case-insensitive variant, which would also find “r” in A1. However, you should turn off all pattern matching in Tools>Options>Calc>Calculate, otherwise you may get confusing results.
1 Like