Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search for hard returns in cells (2000 or 2003)

    Good afternoon all. I am working with an excel spreadsheet full of data. In some of the cells the user has managed to insert a hard return, and I need to search for all such characters and delete them. I cannot find a way to search for that...could anyone offer any advice?

    Thanks in advance...

    klyjen

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search for hard returns in cells (2000 or 2003)

    I don't know of a way to do this interactively. It can be done in VBA. You can paste the following instruction into the Immediate window (in the Visual Basic Editor), position the insertion point in the instruction and press Enter. You can also create a macro with this instruction as contents:

    Cells.Replace What:=vbLf, Replacement:="", LookAt:=xlPart

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Search for hard returns in cells (2000 or 2003)

    edit -replace
    Find: <alt>0010 [Hold alt key and enter 0010 from numeric keypad]
    replace: [Leave it blank]
    <Replace all>

    If you want to do more than one sheet, select the sheet tabs by holding ctrl (individual) or shift (first/last) [Like in explorer] and you can find/replace more than one sheet at a time.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for hard returns in cells (2000 or 2003)

    HansV: that worked perfectly; thanks so much! I did not realize that I could reference a hard return in Excel VBA the same way I can in Word VBA. This one is definitely noted for future reference...

    klyjen

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search for hard returns in cells (2000 or 2003)

    I'm glad it worked. Steve's solution is even better, since it works in the standard Find and Replace dialog.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Search for hard returns in cells (2000 or 2003)

    Steve...thanks! I had used the other solution before I saw yours, but yours is better for regular users (especially) whom I want to keep out of the VBA editor at all costs. Again, I appreciate your quick response...

    jen

Posting Permissions

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