Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Find/Replace Carriage Returns?

  1. #1
    Join Date
    8th June 2004
    Posts
    9

    Find/Replace Carriage Returns?

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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717
    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)

  3. #3
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    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,

    VB:
    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
    Last edited by Dave Hawley; November 3rd, 2008 at 09:45.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  5. #5
    Join Date
    8th June 2004
    Posts
    9
    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    5th August 2003
    Posts
    531
    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:

    VB:
    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
    Last edited by Dave Hawley; November 3rd, 2008 at 09:46.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    8th June 2004
    Posts
    9
    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th June 2004
    Posts
    9
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: July 26th, 2007, 05:43
  2. Carriage Returns
    By dugong in forum EXCEL HELP
    Replies: 2
    Last Post: June 20th, 2007, 00:36
  3. Dealing with Carriage Returns
    By Boony in forum EXCEL HELP
    Replies: 4
    Last Post: September 8th, 2005, 06:39
  4. Stripping carriage returns
    By covetous_kid in forum EXCEL HELP
    Replies: 2
    Last Post: August 15th, 2005, 23:27
  5. Replace line feeds & carriage returns
    By fdgsogc in forum EXCEL HELP
    Replies: 2
    Last Post: August 25th, 2004, 03:27

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno