NeoOffice 2017 Upgrade Issues

From NeoWiki

Revision as of 01:40, 21 August 2017 (edit) ( | contribs) (→Spreadsheet formulas display #VALUE! instead of a number - Update regular expression) ← Previous diff		Revision as of 02:30, 21 August 2017 (edit) (undo) ( | contribs) (→Spreadsheet formulas display #VALUE! instead of a number - Add N() function option) Next diff →
Line 5:		Line 5:
	If a mathmatical formula references any cells that contain spaces or other non-numeric characters, the LibreOffice 4.4 code used by NeoOffice 2017 (as well as all recent versions of OpenOffice and LibreOffice) will display <tt>#VALUE!</tt>. In contrast, the OpenOffice 3.3 code used by earlier versions of NeoOffice would behave as if such cells were empty cells.		If a mathmatical formula references any cells that contain spaces or other non-numeric characters, the LibreOffice 4.4 code used by NeoOffice 2017 (as well as all recent versions of OpenOffice and LibreOffice) will display <tt>#VALUE!</tt>. In contrast, the OpenOffice 3.3 code used by earlier versions of NeoOffice would behave as if such cells were empty cells.
-	To stop the display of <tt>#VALUE!</tt>, you will need to delete cells with spaces or other non-numeric characters that are referenced by any mathematical formulas in your spreadsheet. You can delete most of the cells with spaces or other non-numeric characters using the following steps:	+	To stop the display of <tt>#VALUE!</tt>, there are two options:
-	*Select the cell range which contains text strings.	+	# Put each cell reference in a mathematical formula into the <tt>N()</tt> function. The <tt>N()</tt> function will return <tt>0</tt> if the cell reference is non-numeric which is very similar to the OpenOffice 3.3 behavior.
-	*Choose the {{menu|Edit}} menu then the {{menu|Find & Replace…}} sub-menu.	+	# Delete cells with spaces or other non-numeric characters that are referenced by any mathematical formulas in your spreadsheet. You can delete most of the cells with spaces or other non-numeric characters using the following steps:
-	*In the {{window|Find & Replace}} window, enter "'''^.*[^0-9\-.,].*$'''" (without quotes) in the {{section|Search For}} field.	+	#* Select the cell range which contains text strings.
-	*Set the {{section|Replace With}} field to empty.	+	#* Choose the {{menu|Edit}} menu then the {{menu|Find & Replace…}} sub-menu.
-	*Click {{button|Other options}} or {{button|More options}} button.	+	#* In the {{window|Find & Replace}} window, enter "'''^.*[^0-9\-.,].*$'''" (without quotes) in the {{section|Search For}} field.
-	*Check the {{prefName|Regular expressions}} checkbox.	+	#* Set the {{section|Replace With}} field to empty.
-	*Set the {{prefName|Search in}} listbox to {{button|Values}}	+	#* Click {{button|Other options}} or {{button|More options}} button.
-	*Click {{button|Replace All}}.	+	#* Check the {{prefName|Regular expressions}} checkbox.
		+	#* Set the {{prefName|Search in}} listbox to {{button|Values}}
		+	#* Click {{button|Replace All}}.

---

Revision as of 02:30, 21 August 2017

NeoOffice 2017 is based on the LibreOffice 4.4 code. Because earlier versions of NeoOffice were based on the much older OpenOffice 3.3 code, users may find NeoOffice behaves differently after upgrading to NeoOffice 2017. Below are the most common issues that we have seen after upgrading to NeoOffice 2017:

Spreadsheet formulas display #VALUE! instead of a number

If a mathmatical formula references any cells that contain spaces or other non-numeric characters, the LibreOffice 4.4 code used by NeoOffice 2017 (as well as all recent versions of OpenOffice and LibreOffice) will display #VALUE!. In contrast, the OpenOffice 3.3 code used by earlier versions of NeoOffice would behave as if such cells were empty cells.

To stop the display of #VALUE!, there are two options:

1. Put each cell reference in a mathematical formula into the N() function. The N() function will return 0 if the cell reference is non-numeric which is very similar to the OpenOffice 3.3 behavior.
2. Delete cells with spaces or other non-numeric characters that are referenced by any mathematical formulas in your spreadsheet. You can delete most of the cells with spaces or other non-numeric characters using the following steps:
   • Select the cell range which contains text strings.
   • Choose the Edit menu then the Find & Replace… sub-menu.
   • In the Find & Replace window, enter "^.*[^0-9\-.,].*$" (without quotes) in the Search For field.
   • Set the Replace With field to empty.
   • Click Other options or More options button.
   • Check the Regular expressions checkbox.
   • Set the Search in listbox to Values
   • Click Replace All.