+ 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. 09-28-2013, 11:36 AM #1
    jaimelwilson
    jaimelwilson is offline
    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.
    Reply With QuoteRegister To Reply
  2. 09-28-2013, 12:59 PM #2
    jaimelwilson
    jaimelwilson is offline
    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.
    Reply With QuoteRegister To Reply
  3. 09-28-2013, 01:32 PM #3
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,365

    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 - Retired Excel/VBA Consultant

    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

    Reply With QuoteRegister To Reply
  4. 09-28-2013, 02:48 PM #4
    jaimelwilson
    jaimelwilson is offline
    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!
    Reply With QuoteRegister To Reply
  5. 09-28-2013, 05:48 PM #5
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,365

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

    You're welcome. Thanks for the rep.
    Reply With QuoteRegister To Reply
  6. 10-04-2013, 08:44 PM #6
    jaimelwilson
    jaimelwilson is offline
    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!
    Reply With QuoteRegister To Reply
  7. 10-04-2013, 09:17 PM #7
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,516

    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.
    Reply With QuoteRegister To Reply
  8. 10-04-2013, 09:38 PM #8
    jaimelwilson
    jaimelwilson is offline
    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!
    Reply With QuoteRegister To Reply
  9. 10-04-2013, 09:48 PM #9
    jaimelwilson
    jaimelwilson is offline
    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!
    Reply With QuoteRegister To Reply
  10. 10-04-2013, 09:54 PM #10
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,516

    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.
    Reply With QuoteRegister To Reply
  11. 10-04-2013, 09:56 PM #11
    jaimelwilson
    jaimelwilson is offline
    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!
    Reply With QuoteRegister To Reply
  12. 10-04-2013, 10:07 PM #12
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,516

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

    You are welcome and thanks for the rep.
    Reply With QuoteRegister To Reply
  13. 10-04-2013, 10:23 PM #13
    jaimelwilson
    jaimelwilson is offline
    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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to enter single char data into Excel worksheet without hitting Enter
    By vbarookie in forum Excel General
    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. [SOLVED] 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. 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1