+ Reply to Thread
Results 1 to 5 of 5

Split cells long text with Max 40 character to multiple row using Macro

  1. #1
    Registered User
    Join Date
    08-22-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Split cells long text with Max 40 character to multiple row using Macro

    Help needed.

    I have a large excel file with ~50K lines to limit one of the cells long text to multiple rows.
    Example is attached.

    I have try to put up a macro but I have problem to split the cells with proper word splitting.

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: Split cells long text with Max 40 character to multiple row using Macro

    Try this,

    Please Login or Register  to view this content.
    Edit* I just re-read your post, this won't work for over 4000 lines in the array. But if you can tell me if this macro does what you need, I will amend the code so you can use it on 50,000+ lines.
    Attached Files Attached Files
    Last edited by JapanDave; 08-23-2012 at 08:24 AM.
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,376

    Re: Split cells long text with Max 40 character to multiple row using Macro

    as an option
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-22-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Split cells long text with Max 40 character to multiple row using Macro

    Thanks Japan Dave and nilem for you help.

    However, JapanDave's file, i believe it still needs some modification to work.
    And nilem's macro file work perfectly. I use this to run now.

    Just 1 last question for nilem, i try to copy the code into my excel file. It shows error on the command "Split" but it work perfectly in your file.
    What is the reason? and how to make it work in my excel??

  5. #5
    Forum Contributor
    Join Date
    07-23-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    211

    Re: Split cells long text with Max 40 character to multiple row using Macro

    Hi Nilem

    Your code is brilliant! Could you explain how it's built/structured because I would like to adapt it for my project. I have a textbox in a userform which has a long text and I want to do what your code does without the additional cells.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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