+ Reply to Thread
Results 1 to 26 of 26

Automatic line break after 60 character & move the remaining characters to the next row

  1. #1
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Automatic line break after 60 character & move the remaining characters to the next row

    I have a column of data called (Desc). This data was imported out of QuickBooks (QB). QB would allow up to 255 characters on one line. I have to import this data into another software, which will only allow 60 characters per line.

    I would like to know, is it possible to run a macro or formula or other option that would scan each row of data and break that data line after 60 characters and then insert a empty cell (same column) below the original cell and insert the remaining data up to 60 characters.

    I cannot bring in the data if the line break's remaining data is moved to another column. They have to stay in the same column and I have to tag two identifiers to them so they get imported to the correct customer file.

    I am using Excel 2013 on Win 7 Pro box.

    I have uploaded an example of the Excel file, but I am having trouble attaching it to this thread. I will get it on it asap.

    Thank you for anyone taking time to review my thread.

    Crw
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    If the result can be in Column B.
    Please Login or Register  to view this content.
    You can put the result in any free column by changing the Column in the code.
    You can also delete the Column with the data (Column A in your example) after the code has done it's thing.
    If you do that, it's gone forever though.

  3. #3
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Jolivanes,

    Thank your for your quick post to my issue. I am out of office cutting and baling hay for the farm this weekend. I will be back on Monday and will try your design.

    Again, thank you for your help. I will post my results for you.

    Crw

  4. #4
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Good work.
    If you need any more help, let us know.
    Regards

  5. #5
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Jolivanes,

    Tested out the macro on those two lines. It did break them the line data as you indicated. Funny thing, on that second line, when it got to the numbers, when it put that data into a new cell the row height was changed to 150 instead of 15. I tried removing the "underscores" (thinking that they were giving a command I didn't know, but no change. Did you get the same results when you tried it? Also, do to the size restriction on attachments, I could not send you an good representation of what I am working with. Could I email one customer's data so my next question would make sense.

    Again thank you

    Crw

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    "the row height was changed to 150" Is that cell set to "Wrap Text"?
    Copy the data into a Text file and attach it.
    Or PM me with your email address.
    In the meantime, copy your data into attached workbook and try it.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    jolivanes,

    I have attached a "txt" copy of one of the customer's data that I have to bring in. The identifiers that the 3rd party software requires for me to bring in the data are:

    CustNo (must be 7 numbers)
    LocNo (must be 5 numbers, if the customer has more than one location, that location number is increased by one)
    Counter (starts at 01 & goes up one for each line of Desc. So it a single line is broken up into three rows of 60 characters, then they would be 01, 02, 03 under Counter)
    Date (this is the date of the invoice. It must match the invoice number and date the I pull in from the History fields.)
    Source (this is a build in code in the software, H stands for History)

    So the field that is the most important is Desc (which stands for the History of work done) This is restrictive to 60 characters that can be brought in on one cell, Thus that is why there is a Counter field)

    Ok, when I run the macro against the Desc fields, once that one line is broken up to how many 60 character cells, then I have to rematch the required identifiers to them.

    The original data spreadsheet was imported out of QB. I then had to copy the lines of data out that was needed for the import into another spreadsheet.

    I would be happy to provide you with a page or so out of the original sheet for your reference.

    Plus, I would really like to improve my skills on macros. Can you suggest where to look?

    Thank you

    Crw
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Try this on a copy of your original if it looks anything like the attached.
    The attached has the text file from post #7 copied into Sheet1 and also has this code.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    jolivanes,

    Thank you for your reply. I am down with the flu and have not been able to work. I hope to be back in shape tomorrow and I will try it then.

    Regards

    Crw

  10. #10
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    No Problem. Have a couple hot toddies and you'll be up and around soon
    Get well

  11. #11
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Jolivanes,

    I am back and "alive". Testing what you sent. Will update you with the results. Thank you

    Crw

  12. #12
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Jolivanes,

    We had one heck of a storm yesterday. Knock power off everywhere. So I have been getting the company backup and running. I just ran your macro and this is what I see.
    Every column's data restructures perfect when I click on Button 1.

    The CustNo, LocNo, Invoice, Date, Source, Code all show the correct matching info. The Desc column breaks down the data to the correct number of characters. But when the Desc row is broken down to several cells, the Counter column is suppose to start at 01 and increase by one for each row of cells. When I run the macro, it takes the existing original Counter number and copies it to the next cell which is showing the new data placed from the 60 character break.

    I don't know if it is possible to have the Counter increase by one for each line when it is broken down.

    NOTE: The Counter restarts back to 01 for each new Invoice number.

    Again, Thank you for all of your help.

    Crw

  13. #13
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Change this line after the "Next j" statement
    Please Login or Register  to view this content.
    to these 3 lines
    Please Login or Register  to view this content.
    and see if that results in what you need

  14. #14
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Jolivanes,

    I inserted the new code and got some unexpected results. When it broke the Desc line to 60 characters, it put a blank line below the first line of Desc. It then started the counter on that blank line and repeated the same Counter number.

    I have attached a copy of the results.

    Thank you

    Crw
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    I don't know what you did but this works for me.
    When I ran the code on your attachment from post #14, it worked fine.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    J,

    Replying from my phone. Is it possible that my Excel 2013 is setup to react differently? I am not referring to the spreadsheet.

  17. #17
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    I am using version 2013 Excel. What version are you writing the script in?

  18. #18
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    cwhitley code.jpg

    I could not add the code so tried as a picture

    Replace the previous code with this and see what happens
    Let us know if that helped

  19. #19
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    I am using Office 2013 (Excel) would that make a difference?

    What version and script are you writing in, just curious.

  20. #20
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    I tried both in 2007 (32 bit) and 2013 (64 bit). Works like a dandy in both
    Have you changed the code as in Post 18?

  21. #21
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Jolivanes,

    I have keyed in the code and ran it. I have attached the xls for your review.

    It starts off good (Counters) but then changes by line nine. I am not sure what I did wrong.

    Also, I tried to put the Undo on the module that I did, but I don't think I have it correct.

    Please let me know your thoughts

    Regards

    Crw
    Attached Files Attached Files

  22. #22
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Works fine for me if the right code is assigned to the right button.
    I have changed all the counters to one so it is easier to check after the macro is ran.
    You can change them back to whatever after you have checked that all is in order.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Jolivanes,

    Are you part of the Commercial Service group? I am running out of time to get this done.

    Crw

  24. #24
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    No I am not.

  25. #25
    Registered User
    Join Date
    06-21-2016
    Location
    Stanley, NC
    MS-Off Ver
    2013
    Posts
    15

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    Testing again on what you sent back. I hope I did not offend you when I asked about the Comm Service group.

    Crw

  26. #26
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,497

    Re: Automatic line break after 60 character & move the remaining characters to the next ro

    When I run the code, it will increase the counter by one for every row added.
    Like I mentioned previously, if you change all the counter numbers to a 1 (one) it is very easy to check.
    That is also the reason I have the length counter in Column P. You can cross check if there is another added line (60 is the magic number)
    If it shows 60 then the next line is part of the previous line. (Unless the line happens to have 60 characters originally)
    No, I was not offended at all.
    Good luck

    Have you noticed the extra spaces in the 4th, not counting the headers, line in your text attachment in Post #7.
    Is that what you mean by "but then changes by line nine" which could be the end of that long sentence.
    The line in the code that deletes the space characters as in attached example is the line that has Replace twice.
    I have not seen any other oddities in your example but what your actual text file has, I do not know.
    Last edited by jolivanes; 07-12-2016 at 06:05 PM.

+ 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: 12
    Last Post: 06-22-2016, 01:11 PM
  2. Unicode character creates a line break
    By mikke in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-15-2015, 08:48 PM
  3. [SOLVED] Insert a line break (CHAR(10)) after every 42 characters
    By Rajkiran557 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2014, 06:08 AM
  4. VBA to replace special characters with line break
    By saranyabi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-06-2013, 03:11 PM
  5. [SOLVED] Break a line of text at specfied character count
    By kazphilips in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-14-2013, 12:45 AM
  6. Macro Add line break after character
    By Tybalt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-01-2013, 09:08 PM
  7. Automatic Line Break at X number of characters
    By duyane in forum Excel General
    Replies: 3
    Last Post: 07-17-2012, 02:12 AM

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