Results 1 to 11 of 11
  1. #1
    slundy
    Guest

    Removing the line feed in an Excel Cell

    Is there a vba script, or some code that i can use to remove the line feed or carriage return (Chr(10)) in a cell, or a range of cells? (Shows up as a small square). I have seen one that will remove the hidden apostrophe, but i'm not a vba programmer so I have no idea how to change it for my needs. Any help would be greatly appreciated.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Removing the line feed in an Excel Cell

    Hi,
    You can use something like this:
    Sub ClearNulls()
    Selection.Replace What:=Chr(10), Replacement:="", lookat:=xlPart
    End Sub
    Just highlight a column or range and then run it. (You might want to try different characters - I do a lot of importing from Oracle which produces lots of little boxes but I need to use Chr(9) rather than Chr(10) to remove them. Try selecting a cell that just has the little box in and running
    Sub CheckChar()
    msgbox asc(selection)
    end sub
    to check what the character is)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    slundy
    Guest

    Re: Removing the line feed in an Excel Cell

    When i run the CheckChar it comes up with a msg box that says "74"...

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Removing the line feed in an Excel Cell

    What font is the cell formatted in? 74 should be a 'J'!
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    slundy
    Guest

    Re: Removing the line feed in an Excel Cell

    ok this is gonna sound weird, but what i ended up doing was changing the code everytime i ran it. It didnt' remove all of them when i used chr(10) so I changed it to chr(13), that removed a few more, then changed it to chr(9), and that ended up removing all the rest. Seems like I have to do it multiple times...any ideas?

  6. #6
    slundy
    Guest

    Re: Removing the line feed in an Excel Cell

    Well J was the first letter that was in the cell, maybe that's why....i'll try another...

  7. #7
    slundy
    Guest

    Re: Removing the line feed in an Excel Cell

    My fault, it does come up with "13" when I do a cell that has JUST the square in it.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Removing the line feed in an Excel Cell

    If that got all of them then you can change the code to read:
    Sub ClearNulls()
    With Selection
    .Replace What:=Chr(9), Replacement:="", lookat:=xlPart
    .Replace What:=Chr(10), Replacement:="", lookat:=xlPart
    .Replace What:=Chr(13), Replacement:="", lookat:=xlPart
    end with
    End Sub
    and it should do all of them in one go!
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    slundy
    Guest

    Re: Removing the line feed in an Excel Cell

    one more question, how can i save this as a macro that can be used on any sheet, because we are getting more and more data and i would hate to have to make a new macro everytime, or is that how it's gonna have to be done?

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Removing the line feed in an Excel Cell

    If you have a personal macro workbook, copy it into a module in there. Otherwise, choose Tools-Record Macro and choose to store it in a personal macro book (this will automatically create a workbook in your XLStart directory), stop recording without doing anything, then you can paste the macro into that workbook in the VBEditor.)
    If that's unclear let me know and I'll try and explain it better!
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    slundy
    Guest

    Re: Removing the line feed in an Excel Cell

    clear as a bell, thanks again, you just saved me a ton of work....have a good one!

Posting Permissions

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