File Conversion: Remove embedded returns from the Address cell in an Excel file

The instructions in this technote will help you get rid of returns that are embedded in the Address field of an Excel file. Returns that separate the address from the address2 in a single cell will cause AccuZIP6 to see that return as an End-Of-Record delimiter, which could cause the address2 or city/state/zip information to appear in the next record's name field. The instructions below require using Microsoft Word and Microsoft Excel to fix the file prior to opening it in AccuZIP6. You’ll have to export it out of Excel as a tab delimited text file and open that file in Word. You’ll use Word to remove the excess returns.

1. Open the file in Excel and add a new column after the final field in your list, and fill that field with five tildes (~~~~~) like this:

2. Save the file as text (tab delimited).
3. Open the text file in Word.
4. Pull down the File menu to Save As. Choose [b]Plain Text[/b].
5. Choose [b]MS-DOS encoding[/b]. Hit Save. Close the file.
6. Open the text file again in Word.
7. In Word, replace a return (^p) with a space. Click Replace All.

8. Replace five tildes (~~~~~) with a return (^p). click Replace All.

9. Finally, replace a return followed by a space with a return. Click Replace All.
10. Close the file and click Yes to save the changes.

Now that text file can be opened in AccuZIP6 with File > Open List.

« Back to Tech Notes