Delete carriage return from excel 2008 .xls file

[buzfar]

Hi;

I use OS 10.5
Using Excel 2008

I have a spreadsheet that has carriage returns. I need to remove them in all the cells. It is an .xls with a ton of data. When I am converting this to a tab delimited file, the returns mess up the data.

Is there a simple way to remove the carriage returns from the whole spreadsheet, or at least an entire column?

Thank you!!!!!!

 

[AikiPhilip]

Removing carriage returns

Hi there,

You might try this:

• Insert a new column to the right of the one with the carriage returns
• Click in the blank cell to the right of the first cell in the column that has carriage returns. For this example, let's pretend the cell is A1. Type the following formula in the blank cell:

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

(Note: 13 is ASCII for a carriage return)

• Press the ENTER key. The data in the new cell should be exactly like the data in A1, except without any carriage returns
• Now, select all of the cells in the new column below the cell where you just entered the formula. Use the FILL DOWN command to fill the formula all the way down the column.
• If everything looks OK, delete the column with the carriage returns in it.

NOTE 2: you may have to click on the A1 cell instead of typing it so that Excel thinks you are referring to a cell's position relative to the new one and not to a specific cell. I'm not sure. If all the values in the new column of cells look the same, then you do.

Hope that helps

 

[buzfar]

Still battling this. When I create a new column and insert the data, it works fine on the spreadsheet, but I am also using this spreadsheet for a data merge with InDesign, and it will not work right with that!

Any other ideas?

Thanks

 

[peetah]

I hate to say it, but I don't think it's possible to do this in Excel 2008. The only solution I can give you is to load the spreadsheet in Excel 2007 or 2003 (on a Windows system), find/replace there, save, and reload it in Excel 2008.

It sucks but its the best that I can suggest.

 

[noaaflan]

Old thread

Old thread, I know... But you may have to copy the formula row that you made to remove the carriage returns and right click "paste special" and click "values" to paste the displayed info. My guess is that inDesign is trying to read the code "=SUBSTITUTE(A1,CHAR(13),""" rather than the displayed information.