Find/Replace Carriage Returns?

[Bernard2o6]

Is there anyway to use Find/Replace to delete all the carriage returns in a column of cells? Thanks in advance!

[Dave Hawley]

Hi Bernard2o6

Welcome to ozgrid.com

Here is the best way I know of.

Select any cell that has the character you want removed.
Form the Formula Bar highlight the character only then copy (Ctrl+C).
Now go to Edit>Replace.
In the Find what: box paste (Ctrl+V) the clipboad content.
Leave the Replace with: box empty and click Replace All.

To find out the character code you can paste it into an empty cell then reference it with the CODE function. For example

=CODE(A1)

[Andy Pope]

Hi Bernard2o6,

Here is a VBA alternative. Select the column that needs the replacement applied to.
Now go to the VBE (ALT+F11) immediate window (CTRL+G) and enter the following,

Selection.Replace chr(13)," " 

which will replace carriage_returns with a space.

Cheers
Andy

ps: Dave, I could not get the copy/paste to work with CR's

[Dave Hawley]

Hmmm, you are right Andy. Could have sworn I have done this without VBA before.

[Bernard2o6]

Thank you for responding so quickly Dave and Andy!

Andy-
I'm quite new to utilizing VBA for Excel. Could you tell me the next few steps after entering Selection.Replace chr(13)," " in the immediate window? I'm not quite sure what I'm to do next.

Dave-
Thanks for trying. I too was hoping that this could be done without VBA since I don't have any experience with VBA.

Regards,

Bernard

[jmhans]

Bernard -

What you should probably do is highlight all the cells from which you want to delete the carriage returns first, then go into VBA and find the window for the sheet you are working on (or any Module for that matter).

Type in the following:

Sub delchrs() 
    Selection.Replace chr(13), " " 
End Sub 

Then, with the cursor anywhere within that code go to "Run" > "Run Sub/UserForm"
This should do the trick. Post back if it doesn't work.

-jmhans

[Bernard2o6]

Hi jmhans-

I believe the macro ran, but I believe there are still carriage returns. Please forgive my lack of understanding, as far as I know, I'm doing everything correctly. Will there be some confirmation that the macro ran?

Please advise. Thanks again!

Regards

[Andy Pope]

Perhaps you could post an example workbook with a few records that fail the methods suggested.

Assuming the code was run then maybe the carriage return is actually something else.

Cheers
Andy

[Bernard2o6]

Andy-

Here's an example of what I cut and paste into a cell:

Example List
<P>
<B>Applications:</B>
<UL>
<LI>All cars
</UL>
<P>
<B>Installation Tip: Check local laws.</B>
<P><I>Sold indivuduall</I><P>

There are a lot cells like this in the column. I'm just trying to delete all the carriage returns with a short cut. They are formatted with HTML tags so that we can upload it into our database.

Thank you again for your help!

[Andy Pope]

I would hazard a guess that the carriage return is in fact a linefeed.

For this you can use the Find/Replace dialog.
Select the column and then press CTRL+H to display the dialog.
In the Find What: text box enter the following value ALT+0010 making sure to use the number keypad. You will not see anything appear in the textbox.
You can leave the Replace With: blank.

You may also have to remove the wrap setting for the cells to get it to appear as a single height cell.

Cheers
Andy