+ Reply to Thread
Results 1 to 14 of 14

Allow text to spill into adjacent cells

  1. #1
    Registered User
    Join Date
    03-09-2011
    Location
    Dubbo, NSW
    MS-Off Ver
    Excel 2003
    Posts
    20

    Allow text to spill into adjacent cells

    Hi All,

    Hopefully someone can assist me.

    I have 2 worksheets. "Sheet1" and "Sheet2".

    Sheet1 has a column containing a string of user entered comment text.

    I can successfully use a macro to copy this column from Sheet1 to any given column in Sheet2.

    The issue I have is that I have 3 columns, which represent 3 separate lines on a form in Sheet2. These columns each have a character limit of 29.

    What I want to do is find a way of copying the text string from the column in Sheet1 and pasting the first lot of 29 characters into a column in Sheet2 (Column B for example), the second lot of 29 characters into the next column (Column C for example) and the 3rd lot of 29 characters into the next column (Column D for example).

    Is this possible?

    Thanks in advance.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,869

    Re: Allow text to spill into adjacent cells

    Here's the idea, I can be more specific if you post the code you have so far:
    Please Login or Register  to view this content.
    I would assume you want a loop around that to iterate more than one row.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-09-2011
    Location
    Dubbo, NSW
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Allow text to spill into adjacent cells

    Hi 6StringJazzer,

    Thanks for your reply.

    I had a play last night and ended up finding a formula which allows the text to be split but keeps words whole.

    One example is:

    =TRIM(LEFT(LEFT(G16,29),FIND("^^",SUBSTITUTE(LEFT(G16,29)," ","^^",LEN(LEFT(G16,29))-LEN(SUBSTITUTE(LEFT(G16,29)," ",""))))))

    The formula was good with this, but has given me a new headache. What I've now done is to split the results up again and again in a new "Staging Sheet". The problem now is that I get some #VALUE! errors in the cells, and when I copy them to a blank column (ready for macro copy into the final sheet, the text #VALUE! stays. I cant seem to find a reliable way to search for and then clear the #VALUE! text string. This is the last hurdle, as once I've cleared this, I will be able to copy the clean data in 3 columns into the final sheet.

    Appreciate the help on this one.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,869

    Re: Allow text to spill into adjacent cells

    I'm a little confused trying to understand how what you describe in your second post relates to the first. You start out talking about code then you switch to a formula. If you're using VBA then you may as well do everything in VBA. It is rather straightforward to split a string up into words in VBA (you didn't mention anything in your first post about needing to keep words whole).

    At this point I would need to see your workbook to offer any concrete advice, or at a minimum a more detailed description of what formulas you are using, where they are, how the data is laid out, and your VBA code.

  5. #5
    Registered User
    Join Date
    03-09-2011
    Location
    Dubbo, NSW
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Allow text to spill into adjacent cells

    Hi 6StringJazzer,

    I think I have added quite an unnecessary level of complexity to what I'm trying to achieve, by doing it the way I've done it.
    I am copying the data from the description cell in one sheet to a cell in another sheet, and also making other copies within the second sheet with this code:

    Please Login or Register  to view this content.
    After the text arrives in the cell on the second worksheet, formulas split it up into the 3 lots of 29 characters (it is these cells that are copied for the next Macro to be run). One of these formulas was given in a previous post. I am most happy to do all this in VBA but don't know how.

    The issue with the formula is that some of the results are given as a #VALUE! as there is no text to be copied into them. It is this #VALUE! text string that I am having a huge problem trying to weed out using VBA in a Macro.
    Last edited by shg; 03-14-2011 at 12:13 PM. Reason: fix code tag

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,869

    Re: Allow text to spill into adjacent cells

    First, note that the closer for the code tag is a forward slash, you used a backslash.

    Here is VBA code that should be put into the Worksheet module for the worksheet containing the original strings. It could be modified to put somewhere else if desired.

    It will read strings in the first column of a worksheet until it finds a blank cell. Each string will be broken into three strings and put in the first three columns of the second sheet. Each string is built by adding "words" until addition of the next word would cause it to go over 29 characters.

    There is no error message if the length of the original string is too long to fit into the three strings. There is no error message if it fits into fewer than three.

    Change code to suit where noted. The code has parameters so you can easily change the number of characters in each string and the number of strings.

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    03-09-2011
    Location
    Dubbo, NSW
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Allow text to spill into adjacent cells

    Hi 6StringJazzer,

    Thanks for this code.

    One issue I have is that, as this is a set of delivery instructions (special notes if you like) they live in Column I of the original data, but not each row has data inside this field. If we are checking until the first empty cell, we will likely miss the rest of the data.

    Is there a way to use Column A as the "Check". I.e. If there is text in Column a (for this example it is the first name of the recipient), then keep going...until column A contains no text.

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,869

    Re: Allow text to spill into adjacent cells

    Change this line of code as indicated (although I didn't take the time to test it)

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    03-09-2011
    Location
    Dubbo, NSW
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Allow text to spill into adjacent cells

    Thank you 6StringJazzer.

    I have broken one thing however

    As my first row contains headers, when I use Range("A2") it only refers to cell A2. I must be dumb, but I cant seem to find any reference to how I specify the Range of Column A in its entirety (or any other collumn for that matter) with the exception of the first row.

  10. #10
    Registered User
    Join Date
    03-09-2011
    Location
    Dubbo, NSW
    MS-Off Ver
    Excel 2003
    Posts
    20

    Re: Allow text to spill into adjacent cells

    Further to this, the destination worksheet also has row 1 as a header. So essentially I would like my range to copy to be from I2 to the I(Last row vith a valid value checked against columnA) and then to get split up on the other sheet across Columns P, Q and R, again starting at row 2, as row 1 is a header.

  11. #11
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,869

    Re: Allow text to spill into adjacent cells

    Quote Originally Posted by DDR View Post
    As my first row contains headers, when I use Range("A2") it only refers to cell A2. I must be dumb, but I cant seem to find any reference to how I specify the Range of Column A in its entirety (or any other collumn for that matter) with the exception of the first row.
    See in my code in the first few lines where it says "modify to suit"? Those are the starting cells as indicated. Just change them from A1 to A2. The code loops, going to the next row until it sees a blank row, so you just need to specify the starting point.

  12. #12
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,869

    Re: Allow text to spill into adjacent cells

    Quote Originally Posted by DDR View Post
    Further to this, the destination worksheet also has row 1 as a header. So essentially I would like my range to copy to be from I2 to the I(Last row vith a valid value checked against columnA) and then to get split up on the other sheet across Columns P, Q and R, again starting at row 2, as row 1 is a header.
    Same answer. Change A2 to I2 from fromCell and A1 to P2 for toCell.

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Allow text to spill into adjacent cells

    Have you got a string of words in cells separated by spaces, comma or something/ If you have why not use text to columns?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  14. #14
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,869

    Re: Allow text to spill into adjacent cells

    @royUK: The problem statement is that DDR has strings with spaces, but wants to separate them at the last space before the 29th character, so a bit beyond what text to columns is willing to do.

+ 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