add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
You will sometimes need to import data from external applications that originate in non-Windows operating systems (for example, the DOS operating system). This data is necessary for data analysis and preparing reports in Excel worksheets. Understanding the possible problems and solutions involved in importing text files will help you create organized data tables in Excel. This chapter explains the technique for transferring text files and also offers some common troubleshooting techniques.
Creating a Text File
Most applications that run under the DOS operating system, or under other non-Window operating systems, enable you to save a report with data in a text file by printing the report to a file instead of to a printer.
When you select the Print to file option (in a bookkeeping application, for example), a dialog box opens in which you type a name for the file. Enter the file name in the format of up to eight characters. The operating system automatically adds three characters which represent the file extension – for example, .doc.
Importing ASCII Text Files
1. From the File menu, select Open.
2. In the Open dialog box, select File Type, All Files.
3. In the Search field, select the desired text file, for example, Test.doc.
4. Click OK.
Tip - Copy the file you created into a different folder.
Copying the file into a different folder on your hard drive is recommended. Copy the file to the same folder in which you save the Excel workbook and from which you produce your reports.
Text Import Wizard – Step 1 of 3
1. Select the Delimited option.
2. In the File origin drop-down box, select either DOS or OS/2 for Excel 97 and MS-DOS (PC-8) for Excel versions 2000 and 2002.
3. Click Next.
Text Import Wizard – Step 2 of 3
Step 2 of 3 enables you to organize your data into columns.
1. Separate the data into columns by selecting one of the options. Now check the results.
2. Click Next.
Text Import Wizard – Step 3 of 3
Step 3 of 3 enables you to format the columns.
1. Select a column (the column will be colored black), and then click on the heading of the column.
2. Under Column data format, either select one of the three formats (General, Text or Date) or select Do not import column (skip) to prevent importing unnecessary columns into Excel.
3. Click Finish.
Tip – Format your dates in the import process
If you import a Date column, you must first format this column by selecting the Date option under Column date format. If you do not format the Date column, it will be difficult for you to use the date formatting in the entire worksheet in the workbook.
Troubleshooting problems while importing text
A minus appears to the right of the number
Problem
A negative number that is imported into Excel is often formatted as text with the minus sign (-) on the right side instead of the left. Excel does not sum up negative numbers that are formatted as text, and the results will not reconcile.
Solution
Enter the formula in the figure below to solve both problems. It will move the minus sign (-) to the left side of the number, and it will format the data in the cell as a number instead of as text.
Breakdown of the functions used in the nested formula:
Function
Explanation
Value
Returns a change in formatting from text to number.
If
Checks True or False of the logical value.
Right
Returns the number of characters from right to left in the text.
Left
Returns the number of characters from left to right in the text.
Len
Returns the number of characters in the text.
Data that is not formatted as a number or date
Problem
Often, columns with numerical data or columns that contain date data are formatted as text columns.
Changing the formatting of the column from text to number format is often not efficient.
Solution
Multiply by 1
Enter the number 1 into the cell and copy it. Select the column that is formatted as text, right-click, and select Paste Special. Select Multiply from the dialog box, and click OK.
Text to Columns
Select the column that is formatted as text. From the Data menu, select Text to Columns, and then select the Fixed width option. Skip Step 2. In Step 3, select the General option from the Column data format, and click Finish. To change the column formatting to date formatting, select the Date option (under Column data format), and then click End.
Erase Unnecessary Characters
Trim is an important function that is used to clean up unnecessary empty characters of text that appear in a cell. From the Insert menu, select Function; from the Paste Function dialog box that appears, select Text from the list of Function categories column; and then select Trim from the Function name column.
Create and Add a Function for Reversed Text Characters
Before adding a function, read about techniques for adding functions in Chapter 7, Formulas.
1. Click Alt+F11 to open Visual Basic Editor (VBE).
2. From the Insert menu, select Module (to add a module).
3. Enter the following lines of code into the module:
Testing the function:
1. To test the function, open Paste Function (Shift+F3).
2. From the User Defined category, select the ReverseText function.
3. In the function box, select any cell that contains text.
4. Click OK.