Q+E Converts Numbers to Text if First Field Is Blank

Last reviewed: November 3, 1994
Article ID: Q76278
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.1, 2.5 and 3.0
  • Microsoft Excel for OS/2, version 3.0

Summary:

After manipulating a dBASE file in Q+E, numeric fields will import as text when the file is reopened in Microsoft Excel if the first field in a numeric column is blank.

More Information:

If the first field in a column of a dBASE file is blank, the field type will default to Character in Q+E. Then in Q+E, even if numbers are entered into the field and it is taken back to Microsoft Excel, the fields will appear as text.

Workarounds

  1. Enter a value in the first field before modifying the file in Q+E.

    -or-

  2. Convert the entry in cell A2 to a number, after returning to Microsoft Excel.

    a. Select A2.

    b. Press the Edit Key (F2) and press ENTER.

    -or-

  3. The third is to define the column type in Q+E.

    a. From the File menu, choose Define.

    b. Select the Field name and change the type from Character to

          Numeric.
    

    c. Exit Q+E, saving the changes. This option will allow you to

          convert the entire column to numeric, without having to change
          each record individually.
    

Steps to Reproduce Problem

  1. Enter the following in Microsoft Excel:

          A1      Age
          A2
          A3      4
          A4      6
    
    

  2. Select A1:A4 and choose Set Database from the Data menu.

  3. Save the file in the dBase file format and open it in Q+E.

  4. Choose Allow Editing from the Edit menu.

  5. Enter a number in the blank field and close the file.

  6. Reopen the file in Microsoft Excel.

  7. Enter "=SUM(A2:A4)" (without the quotation marks) in cell A5.

This result is 0.

Reference(s):

"Q+E for Microsoft Excel," version 3.0, pages 45-47


KBCategory: kbother
KBSubcategory:

Additional reference words: 2.1 2.10 2.5 2.50 3.0 3.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.