How Do I Get Rid of Carriage Returns?

**Ocean Zhang** · 06-24-2009, 11:50 PM

When I save my file as text and upload into a program, I get a response saying that my file can't be processed due to carriage returns. What are these things and how do I get rid of them? My excel file has about 6000 rows and 50 columns of data.

**Andy Pope** · 06-25-2009, 05:22 AM

Isn't CarriageReturn 013 ?

**Andy Pope** · 06-25-2009, 06:00 AM

No worries.

**Excel Matic** · 06-25-2009, 06:38 AM

Hey guys,

Can you guys please check - I think carriage return has code as 010 (I have confirmed this on my side)

Please confirm.

**Andy Pope** · 06-25-2009, 06:41 AM

No LineFeed has a value of 10.

**Ocean Zhang** · 06-25-2009, 11:04 AM

I tried the steps with Ctrl-H, Alt 015, replace all, and Alt013 replace all, but I can't tell if it works because I can't spot any carriage returns.

I've attached my files. If you can point out to me where those carriage returns are, I'd really appreciate it.

Also, how exactly would I use the Clean() formula?

Thanks
Ocean

**NBVC** · 06-25-2009, 11:21 AM

It's hard to see from the file you attached... looks like you cleaned them...

Perhaps attach original file too...?

**Ocean Zhang** · 06-25-2009, 11:41 AM

strange... I can't get to the original until later tonight.

I grabbed the text file that failed to validate due to carriage return issues, opened it with excel, saved it as .xlsx, and attached it to this forum. I didn't attempt to clean that one. Strange.

**Ocean Zhang** · 06-25-2009, 11:42 AM

would the process of saving it as text, then converting the text file back to xlsx clean up carriage returns?

**shg** · 06-25-2009, 11:42 AM

It's hard to see from the file you attached..

Cell M403, for example.

If you want to delete them all, do Alt+F11 to open the VBE, Ctrl+G to open the Immediate window, and enter

Please Login or Register  to view this content.

**NBVC** · 06-25-2009, 11:45 AM

Well, I didn't look as thoroughly as shg did

.

.. but hopefully his solution works for you.

**shg** · 06-25-2009, 11:52 AM

Well, I didn't look as thoroughly as shg did.

Please Login or Register  to view this content.

**NBVC** · 06-25-2009, 11:54 AM

Thanks shg... will remember for next time I hope... as you know, VBA route is not my forte

**Ocean Zhang** · 06-25-2009, 12:26 PM

sorry for the REALLY dumb question, but what do I do after I put the code cells.replace chr(13), "" into the Immediate window of the Visual Basic thingy?

**shg** · 06-25-2009, 12:29 PM

Hit Enter.

**Ocean Zhang** · 06-25-2009, 01:25 PM

wonderful. Thanks so much. I guess the carriage return didn't look like anything. It just took up an extra line of space.

does the code cells.replace chr(13), "" have any potential hazards, or does it only get rid of the carriage returns?

Thanks!

**shg** · 06-25-2009, 01:31 PM

It only gets rid of carriage returns, but it will only work in cells with 1024 or fewer characters.

**Ocean Zhang** · 07-01-2009, 12:51 PM

when i run:

cells.Find(chr(13)).Select

I get an error Run-time error 91: Object variable or With block variable not set.

What does that mean? After running cells.replace chr(13), "" in the attached sheet, but I still get a message from the site that I'm uploading to that the file contains extra carriage returns

**protonLeah** · 07-01-2009, 10:54 PM

Usually, what you have been referring to as ' carriage return' is called carriage return-linefeed (CRLF) and consists of two characters, namely, hex(0D), the carriage return code, and hex(0A) the linefeed code. As far as I know, they are always used together:

The carriage return hex(0D) = chr(13) causes the print head/cursor to move to the extreme left of the paper/screen.

The linefeed code hex(0A) = chr(10): For printers, it moves the paper UP one line. For for the screen, it moves the cursor DOWN one line. If you look at a text file in a hex editor you will always see them paired: 0D0A.

So try a replace for all 0D's, ie. chr(13) and 0A's (chr(10))...

**Ocean Zhang** · 07-02-2009, 10:49 AM

That was beautiful. Thanks so much!

How Do I Get Rid of Carriage Returns?

LinkBack

Thread Tools

Rate This Thread

Display

How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Re: How Do I Get Rid of Carriage Returns?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions