[Solved] Err:501 Counting occurrences of character in cell

[morphingstar]

Edit: Split from [Solved] Counting occurrence of specific character in cell because that topic is solved thus morphingstar needs their own topic

Tks Mr P., whatever I do I get error, usually 501 in Libre Office. I use OO Calc mainly for the HELP section, easier than LO.
LEN(cell)-LEN(SUBSTITUTE(cell;"-";"")) x LEN($d$101)-LEN(SUBSTITUTE(cell;”\”;"")) x =LEN(($D$101)-LEN(SUBSTITUTE($D$101,”\”;""))))) surplus brackets added by LO.
I need to count "\" in path of file list
Examples, 1 source cell contains file name, extension, path. The goal is to separate the items into specific columns [FN¦Ext¦Path]
1of 99.jpg jpg C:\Users\ALL\Pictures\_PRINTSCREEN\
danwinter 2h12m46_humLion.jpg jpg C:\Users\ALL\Pictures\_PRINTSCREEN\ancient\
toulouse_peg01.jpg jpg C:\Users\ALL\Pictures\_PRINTSCREEN\ancient\PEG\
There can be more "\", the number varies, = minimum of selected folder, in this case _PRINTSCREEN includes minimum 5 x \.

I pasted the formula from URL into sheet. Entering formulas with keyboard often fails to work in LO. Err501 is unavoidable.
I opened the sheet with OO, the same error appears.

Cell I97 shows the error.
Hoping for a solution. Thanks.

[RusselB]

The SUBSTITUTE function in I97 is missing parameters.

[Zizi64]

Code: Select all

=LEN($D$101)-LEN(SUBSTITUTE($D$101;"\";""))

[Zizi64]

Otherwise the extension PYSTRING has a function named COUNT(). That counts the occurrence of the specific character (or substring) in the passed string:

viewtopic.php?t=83856&p=426777

[morphingstar]

RusselB _ Reason given: Missing parameters. Action: Reduced formula to =SUBSTITUTE("0rgonlab.jpg jpg C:\Users\ALL\Pictures\_PRINTSCREEN\",”\”,"§"). Enter] results in added ")", error501 remains. 3 Parameters: Text to check, character to replace, character replacement. My formula included 3 parameters: Cell, character to replace, character replacement = none, not even space! I wonder what is missing.

Hubert Lambert _ Thanks, if I can not get OO LO formula to work as promised I shall try the Pyton oxt.
I would prefer a correct working formula. Will check with LO Forum. Will let you know the reason of failure.

Thank you both.

[RusselB]

Not sure why, but trying your formula manually entered as you posted it gives me the same results, however, by putting the text for the first parameter into a cell and then referencing that cell as the first parameter, the formula works fine.

[MrProgrammer]

Reduced formula to =SUBSTITUTE("0rgonlab.jpg jpg C:\Users\ALL\Pictures\_PRINTSCREEN\",”\”,"§") … error501 remains

You must use an actual quotation mark in formulas, not the bogus Right Double Quotation Mark.

[Tutorial] Calc formula terms

Also, if you are using OpenOffice 4.1.6, as claimed by your signature, you must use semicolon as the operand separator, not comma. Fix your forum signature, if needed, in User Control Panel → Profile → Edit Signature or you risk receiving inappropriate advice for your situation.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[robleyd]

Crossposted on the AskLO forum.

If you cross post, please let us know that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.