+ Reply to Thread
Results 1 to 9 of 9

Creating a clone line of data to remove the <alt> + Enter.

  1. #1
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Creating a clone line of data to remove the <alt> + Enter.

    I have inherited a project from a previous employee and for the life of me I have no idea of why it did this the way he did.

    Attached is an example of what I am dealing with, unfortunately is a HUGE file so doing this manually is just not an option.

    Sheet 1 has two highlights yellow and green, the corrected output is on Sheet2. Essentially what they did was in Col B is have the part numbers for the product listed using “alt” + enter. This does not work for what I need to do (inventory control) so I need some code that will spit out Row 2 into 4 rows with the same data for each row as depicted on sheet2.

    FYI the actual list is over 1000 lines long and the columns run all the way out to DA….. so you see how huge of a manual project this would be…..

    Thank you for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Creating a clone line of data to remove the <alt> + Enter.

    How about
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Creating a clone line of data to remove the <alt> + Enter.

    If interested, a formula-based approach:

    A2, copied down
    =IFERROR(INDEX(Sheet1!$A$2:$A$9,MATCH(0,INDEX(--(COUNTIF($A$1:A1,Sheet1!$A$2:$A$9)=1+LEN(Sheet1!$B$2:$B$9)-LEN(SUBSTITUTE(Sheet1!$B$2:$B$9,CHAR(10),""))),0),0)),"")

    B2, copied down
    =IF(A2="","",TRIM(MID(SUBSTITUTE(CHAR(10)&INDEX(Sheet1!$B$2:$B$9,MATCH(A2,Sheet1!$A$2:$A$9,0)),CHAR(10),REPT(" ",125)),125*COUNTIF($A$2:A2,A2),125)))

    C2, copied across and down:
    =IF($A2="","",INDEX(Sheet1!C$2:C$9,MATCH($A2,Sheet1!$A$2:$A$9,0)))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Thank you ALL for your wonderful suggestions.

    jindon your replay seems to work the best for me, but the code is really over my head.

    What happens if the number of colulms goes from A to DF. Is there something that needs to be done to the code to accommodate that larger span?
    Last edited by sungen99; 10-12-2019 at 10:46 AM. Reason: adding more info

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Creating a clone line of data to remove the <alt> + Enter.

    You're welcome & thanks for the feedback

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Just try.

    It should work as long as the data range is not separated by the blank column/row and the 2nd column has Line Feed character(s).

  8. #8
    Forum Contributor
    Join Date
    05-05-2004
    MS-Off Ver
    Office 365
    Posts
    651

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Jindon, lets say the range i needed to change was NOT Col B, but say Col D? how could i alter the code to accommodate that?

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Creating a clone line of data to remove the <alt> + Enter.

    Change to
    Please Login or Register  to view this content.

+ 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. [SOLVED] Creating clone worksheet with predetermined name without saving it
    By igormigor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2017, 04:12 AM
  2. [SOLVED] Remove Line-Spacing (Alt-Enter)
    By Keibri in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-29-2015, 07:53 AM
  3. [SOLVED] MultiLine Textbox to auto remove blank line, count line, remove duplicate
    By khhoa in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-24-2015, 09:28 PM
  4. [SOLVED] Need to Remove Alt+Enter from end of line, keep other Alt+Enter in place
    By jaimelwilson in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-04-2013, 10:23 PM
  5. Macro for creating line betwen two points, then remove markers.
    By comp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-10-2013, 12:55 PM
  6. [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
  7. 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