Peachtree Accounting Software Technical Help                 Return to Technical Documents Listing

Separating Data Fields in Delimited Files Using Microsoft Excel 5.0 or greater

Separating Data Fields in Delimited Files Using Microsoft Excel 5.0 or greater


 "The following article contains information on known issues and fixes for Peachtree Contact Manager Powered by ACT!. Because Peachtree Contact Manager is built from the ACT! product, some references to ACT! as the product may still occur."

The procedures outlined below require that you have Microsoft Excel version 5.0 or later and a basic knowledge of its operation. The database must also be exported from the source application as a comma or tab-delimited file.

Note: Make sure to backup the text file prior to importing it into Excel to modify it.

Opening a Delimited Database File as an Excel Spreadsheet
1. Open Excel.
2. Choose File, Open. The Open File dialog box appears.
3. Under List Files of Type, choose Text Files.
4. Change to the drive and directory where the delimited database file is located.
5. Select the file you want to open, click OK. The Text Import Wizard - Step 1 of 3 dialog box appears.
6. Select Delimited File Format, then click Next. The Text Import Wizard - Step 2 of 3 dialog box appears.
7. Choose the appropriate delimiter or delimiters.
8. Click Finish when the Data Preview dialog box displays the data by field in columns. If the database information does not appear in separate columns, choose a different delimiter.

Separating the Fields
Three formulas are necessary to separate the city, state, and zip code into separate fields. The numeric values in the formulas must change according to the format and length of the string being separated. The formulas used in this document require that the city, state, and zip code be in this format:

Eugene, OR 97401

The spacing between the fields is important. Use a single space between the comma and state, and a single space between the state and zip code.

Note: To adjust the numeric values in the formulas for other formats, see your Excel documentation.

Follow these steps to space your city, state, and zip code fields correctly.
1. Select a cell in the column with the combined city, state, and zip code data.
2. Choose Insert, Columns.
3. Repeat Step 2 twice to have 3 blank columns.

Note: If necessary, adjust the column width to display the data and blank columns.

Formula 1
1. Select the empty cell at the beginning of the first empty column to the right of the combined field. Do not press Enter until instructed to do so.
2. Type =MID(
3. Click the cell with the city, state, and zip code. This inserts the cell reference into the formula (for example, F1).
4. Type ,1,(LEN(
5. Click the cell with the city, state, and zip code.
6. Type )-10))
7. Check the entire string for errors. It should read,

=MID(F1,1,(LEN(F1)-10))

8. Press the Enter key. City is the only field inserted into the new cell.

Note: If the data is in a format different from the previous example, the value 10 must be adjusted. The value is the number of characters (in the string) from the comma to the end of the string. For example: zip Plus 4 the equation will be =MID(F1,1,(LEN(F1)-15)) and without a comma after the city =MID(F1,1,(LEN(F1)-9)).

Formula 2
1. Select the first cell in the second blank column to the right of the combined field. Do not press Enter until instructed to do so.
2. Type =MID(
3. Click the cell with the city, state, and zip code.
4. Type ,(LEN(
5. Click the cell with the city, state, and zip code.
6. Type ) -7),2)
7. Check the entire string for errors. It should read,

=MID(F1,(LEN(F1)-7),2)

8. Press the Enter key. State is the only field inserted into the new cell.

Note: If the data is in a format different from the previous example, the value 7 must be adjusted. The value is the number of characters (in the string) from the ending letter of the state code to the end of the string.

Formula 3
1. Select the first cell in the third blank column to the right of the combined field. Do not press Enter until instructed to do so.
2. Type =RIGHT(
3. Click the cell with the city, state, and zip code.
4. Type ,5)
5. Check the entire string for errors. It should read,

=RIGHT(F1,5)

6. Press the Enter key. Zip Code is the only field inserted into the new cell.

Note: If the data is in a format different from the previous example, the value 5 must be adjusted. The value is the number of characters in the zip code or zip+4.

Copying the Formulas for the Database
1. Select the first cell of each of the three columns with the separated city, state, and zip code all at once. (If you need help selecting multiple cells at once, refer to your Excel documentation.)
2. Choose Edit, Copy.
3. Select the range of cells that corresponds with the number of records in the database. Use only the column containing City.
4. Choose Edit, Paste Special and select Formula. The city, state, and zip code fields are now separate in all the records in the database.

Deleting Old Data
1. Select a cell in the column with the city, state, and zip code fields still combined.
2. Choose Edit, Delete. The Delete dialog box appears.
3. Click Entire Column, then click OK.

Saving the Spreadsheet as a Tab-Delimited Text File
1. Choose File, Save As. The Save As dialog box appears.
2. Under Save File as Type, choose CSV (Comma Delimited) (*.csv).
3. Enter a unique name for the file in the Filename box. and click OK. The database information is saved as a comma-delimited text file.

Return to Technical Documents Listing

Keywords: peachtree delimited excel data fields