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.
- Open the file in Excel and press CTRL+A to show all
- Press CTRL+H to perform a Search and Replace
- In the Find What box, type CTRL+J
- In Replace With, press the spacebar
- Click Replace All
- Close the file and click Yes to save the changes.
Now that text file can be opened in AccuZIP6 with File > Open List.
If this doesn't work, try Plan B.
Plan B
Open the file in Excel.
Add a column at the end of your data and fill five tildes into that column, as shown below. This is acting as your end-of-character delimeter.
For example, if ZIP is the final field in your list, create a new column directly after ZIP and populate that field with five tildes. Fill the tildes down the column, which you can do quickly by typing in the tildes into a cell, copying that cell, selecting the entire column and pasting.
Save the file out of Excel as Text (Tab Delimited) (*.txt).
Open that .txt file in Word.
Replace paragraphs (^p) with a space.
Now replace 5 tildes with paragraphs
Save the file, and now you can open it with AccuZIP6.
