"Function Is Not Valid," "Error in Formula" with FLATFILE.XLA

SUMMARY

FLATFILE.XLA is an add-in macro included with Microsoft Excel 3.0 that provides a parsing utility. When parsing a file in Excel, you will receive the error message "Function is not Valid" or "Error in Formula" if both of the following conditions are met:

1. A line in the file begins with an @.

2. The option to remove extra blank spaces is selected from the Smart Parse dialog box.

MORE INFORMATION

The @ character has special significance in Lotus 1-2-3. It precedes each formula, acting similarly to Excel's equal sign. If you attempt to enter the @ character into the first position of a cell in Excel, you will receive the message "Function is not Valid" unless the character is followed by a valid Excel formula, in which case Excel converts the @ character to an equal sign. For example, if you enter

   @SUM(B1:B2)

   =SUM(B1:B2)

To enter the @ character plus a string (other than a valid formula) into a cell, you must use the format ="@string" so that Excel does not attempt to evaluate the contents.

The parsing problems with FLATFILE.XLA (described above) occur when attempting to parse strings that begin with the @ character. A "Function is not Valid" message is returned if a text string follows the @ character. An "Error in Formula" message occurs if the @ character is followed by a numeric value or a text string is preceded by at least one space.

Workarounds

1. The following steps will remove all occurrences of the @ character in the text file.

   a. Select the data to be parsed.

   b. From the Formula menu, choose Replace.

   c. Type @ in the Find What box, and leave the Replace With box blank.

   d. Select Replace All.

   -or-

2. Do not select the option to remove extra blank spaces from the Smart Parse dialog box.

REFERENCES

"Microsoft Excel User's Guide," version 3.0, pages 116-135, 684