Toad World® Forums

Excel Formula Errors must reset Ignored Formula Errors


#1

I recently upgraded to 32bit TDP Pro 4.3.0.718. Everytime I export data out of a Toad (Oracle: Banner) to an Excel file I run into an error in formula issues. In order for me to reset the data types correctly, I have to do the following, so that the data types I transfer into other Excel workbooks are read correctly . Go to the File> Options>Formulas>Section: Error Checking>Click:Reset Ignored Errors. Do I have a setting that I misconfigured?


#2

Hello ajmoehle,

What Excel version you use? Which data type works incorrect?

Regards

Petr


#3

I use Office 365, Excel Version: 1804 (Build 9226.2114 Click-to-Run) Monthly Channel. Before I ran into this problem Excel would identify number like entries as “Number stored as text (NSAT)” and produce the triangles to convert the “errors” to numbers.

Our system, Banner uses any Oracle 11g, so most values DATATYPE are pulled as VARCHAR2 or DATE, with the actual types (various types of strings, ‘Date Time’, ‘Unsigned Integer’, ‘Number’). It seems that Excel had been recognizing the ‘Unsigned Integer’ values as the ‘NSATs’.


#4

One fast way to convert text numbers to real numbers in Excel is to put the value 1 into a cell on the spreadsheet. Then copy that cell and do a paste special/multiply on the column with the text numbers. It will convert all text numbers to numbers via an implicit conversion. Even if there are mixed text numbers and real numbers all will be okay as 1 times anything gives back the same number.