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:
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.
- Photo Credit number background image by kuhar from Fotolia.com
How to Create a Carriage Return Barcode for Microsoft Excel
Microsoft Excel can be used as a limited database for transcribing numbers into other formats. One useful format is the standard bar...
How to Add Carriage Returns in MS Word
Microsoft Word is the most popular word-processing program in the world not just because of its manufacturer’s marketing know-how but also due...
How to Remove a Carriage Return With SED
SED is a powerful stream editor that comes built into all Unix, Linux and Mac OS X computers. It excels at performing...
How to Replace Carriage Returns in WordPad
Carriage returns, also known as line breaks or line ends, force text to start a new line. (They are called "carriage returns"...
How to Remove a Blank Carriage Return Using SQL
Structured Query Language, or SQL, is a language used to interface with relational databases and edit their structure. One part of the...