Q+E: Unexpected Results with Mixed Alpha and Numeric Data

• Q+E for Microsoft Excel for Windows, versions 3.0 and 3.0a
• Q+E for Microsoft Excel for OS/2, version 3.0

SUMMARY

When you open a Microsoft Excel database in Q+E for Microsoft Excel, if the same field in the first twenty-five records of your database contains mixed text and numeric values, the numeric values are displayed with additional decimal points. The text values are displayed correctly.

Also, if the same field of your database contains mixed text and numeric values but there are no text values in the first twenty-five records, the numeric values are displayed correctly but the text values are not displayed.

MORE INFORMATION

When you import a database from Microsoft Excel or a text file, Q+E scans the first twenty-five records of each field to determine the data type for that field. That is, if the first twenty-five records contain text in that field then Q+E considers it a text field; if the first twenty-five records contain numbers, then Q+E considers it a numeric field.

If the first twenty-five records of a field contain both text and numeric values, then Q+E considers it a text field and displays a number with a decimal point and two trailing zeros (nnn.00). If there are no text values in the first twenty-five records but there are text values in records after the first twenty-five, Q+E considers the field to be a numeric field.

For example, in a field containing 5-digit and 9-digit ZIP codes, if Q+E determines that the field is text, the 5-digit ZIP codes will be displayed with decimal points and trailing zeros. If the field is determined to be numeric, the 9-digit ZIP codes will not be displayed.

Workaround

To force Q+E for Microsoft Excel to display mixed numeric and text values in a field, ensure that the field is formatted as text, and that all values are recognized by Q+E as text values.

To ensure that all values are recognized as text, do one of the following:

• Format all your data as text in Excel before opening the file in Q+E.

  -or-

• Save your Excel file in the comma-separated values (CSV) file format. (Saving your file as a CSV file formats all of your data as text.)

To format data as text in Microsoft Excel

1. Open your database file in Microsoft Excel.

2. Insert a new empty column directly to the right of the column containing your mixed data. This column will temporarily hold the formulas that convert your mixed data to all text.

3. In the newly inserted (temporary) column, select the cell in the first record (that is, the first row below your database headings).

4. Type the following formula:

          =TEXT(<cell_ref>,"#")
   

   where, <cell_ref> is the reference to the first cell below your headings in the field you want to convert to text (the column to the left of your temporary column).

5. Copy this formula down the temporary column as far as necessary to convert all the data in the field. This can be done easily by dragging the fill handle of the first cell (containing the formula) down as needed.

6. With the cells containing your formula in the temporary column still highlighted, choose Copy from the Edit menu.

7. From the Edit menu, choose Paste Special.

8. In the Paste Special dialog box, choose the Values option in the Paste group. This replaces your formulas with equivalent text values.

9. Copy your newly created text values and paste them over the old mixed text and numeric values in the original column, then delete the temporary column.

To save your file as in the CSV file format

1. Open your file in Microsoft Excel.

2. From the File menu, choose Save As.

3. In the Save As dialog box, choose CSV from the Save File As Type list.

4. Choose the OK button.