How to Remove Carriage Returns or Line Breaks

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.

Remove Carriage Returns Using Find & Replace Functions
Remove Carriage Returns Using An Excel Formula

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.

2. Press 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.

=SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),", ")

Where:

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