One of the things that trip our importer are carriage returns or line breaks in your Excel file. There are different ways on how you can remove them, but these two are the easiest. Keep on reading to find out how these are done.
Here’s how a carriage return in a cell looks like. If you’ll notice, Cell B1 has the complete address details, but only the first line appears in the Formula Bar.
Remove Carriage Returns Using Find & Replace Functions
1. Highlight all the cells where you want to delete carriage returns.
Ctrl+H to open up the Find & Replace dialog box.
3. Then hit
Ctrl+J in the Find What field, and a tiny dot will appear.
4. Enter any value in the
Replace With field to replace the carriage return (in this case, we’ll use a comma and a space), then hit Replace All.
5. The carriage returns are removed, and you can now
upload your file.
Remove Carriage Returns Using An Excel Formula
If removing them using Find & Replace didn’t work, you can use an Excel formula to delete carriage returns.
1. First, you’d have to add another column in your spreadsheet where you’ll write the formula. You can name it as Address with Formula (this is just an example) to differentiate it from the other one.
2. Then write this formula in the first cell under the
Address with Formula column, and hit Enter.
B2 - is the cell where the carriage return is (NOTE: This will vary depending on where the carriage return is positioned in your spreadsheet) CHAR(13) - is Excel’s character function for carriage return CHAR(10) - is Excel’s character function for line feed
3. Copy and
paste it as formula to the rest of the cells.
4. After that, copy and
paste the results as values in the cells under the Address column.
5. Delete the
Address with Formula column, save your work, and the file is now ready for import.