+ Reply to Thread
Results 1 to 13 of 13

Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    I have a spreadsheet with several cells that contain several pieces of data separated by Alt+Enter. I have already submitted and received a solution for how to split the cell contents to new rows containing single values here:

    http://www.excelforum.com/excel-prog...-new-rows.html

    The problem I am having is that several of the cell contain an Alt+Enter at the end of the line which is causing extra rows to be produced when the marco is run.

    I need a macro to remove only the Alt+Enter at the end of the data in each cell, but keep the other Alt+Enter's that are between text in place.

    I did find a way to do it by reproducing the entire spreadsheet with the formula
    =if(right(a1,1)=Chr(10),left(a1,len(a1)-1),a1)
    and then copy - paste special - values
    I could record this as a macro, but it seems like there should be an easier way to do it using a macro.
    Thanks!
    Last edited by jaimelwilson; 09-28-2013 at 01:05 PM.

  2. #2
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    I found this code which will work for removing the end Alt+Enter from cell B11, but I need to change it to search all cells on a sheet and remove the ending Alt+Enter. Any suggetions?

    Please Login or Register  to view this content.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,909

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    Terrific! Thanks so much!

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,909

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    You're welcome. Thanks for the rep.

  6. #6
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    I realized after running this that there are some lines that have several alt+enters at the end. This only removes the last one (which is what I originally asked for.) Any way to make sure all the alt+enters after the data are removed?
    Thanks!

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,636

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    Do you mean?
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    Exactly, but I need it to do it for the all cells in the whole sheet, not just cell B11. Sorry, I am sure that is a simple change, I am just new to this.
    Thanks!

  9. #9
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    Or if there is some way to replace any string of alt+enter of 2 or more with 1 alt+enter prior to removing the one at the end of the data that would solve a different, but related problem I have.
    Thanks!

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,636

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    This should do
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    You are amazing!! Thank you so much!

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,636

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    You are welcome and thanks for the rep.

  13. #13
    Registered User
    Join Date
    09-20-2013
    Location
    McKees Rocks, PA
    MS-Off Ver
    Excel 2013
    Posts
    19

    Re: Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place

    Any time. You saved me so much time. I already spent a ton of time on google today with no luck.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 12-06-2010, 04:39 PM
  2. Enter missing number on specific place
    By alogoc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-20-2009, 05:03 AM
  3. how do I remove fx from the function line, can't enter data
    By dmdranch in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 12-03-2005, 02:55 PM
  4. [SOLVED] enter a product numbinfo in cel needs to propt other cels to automatically enter text
    By Bill W in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-03-2005, 09:05 AM
  5. [SOLVED] Remove Line Breaks ALT + ENTER
    By Highman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2005, 05:06 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1