How to Remove Carriage Returns From CSV

Save

In some versions of Microsoft Excel, a carriage return (new line) in a CSV or other Excel-compatible file can translate to an unsightly square embedded in the text of the file, making it difficult to read. However, Excel includes a character-substitution function that can remove any character in a cell and substitute it with a character of your choice. Substitute the carriage returns in a CSV file with an empty character to remove the carriage returns from the file.

Things You'll Need

  • Microsoft Excel 97-2003
  • Open the CSV file in Excel. Locate a cell containing carriage returns that you want to remove and record the cell number (e.g., B5).

  • Click in an empty cell somewhere else on the spreadsheet, near the first cell if possible.

  • Click in the formula bar (labeled "=") at the top of the screen. Type:

    =SUBSTITUTE(B5,CHAR(13),"")

    Use the cell number that you recorded in the first step rather than "B5." This command tells Excel to find all carriage returns in the selected cell and remove them. The data from the selected cell appears without carriage returns in the cell that you clicked in the previous step.

  • Right-click the current cell and click "Copy."

  • Select the first cell, right-click it, then click "Paste Special."

  • Select the radio button labeled "Values" and click "OK." This copies the text from the second cell--with carriage returns removed--to the first cell, leaving out the formula that you typed. The second cell can be deleted.

  • Repeat this process for each cell containing unwanted carriage returns.

Tips & Warnings

  • The steps in this article also work in Excel 2007. However, Excel 2007 does not display carriage returns as small boxes as previous Excel versions do.

References

  • Photo Credit number background image by kuhar from Fotolia.com
Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!