Allow text to spill into adjacent cells

**DDR** · 03-09-2011, 11:52 PM

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.

**6StringJazzer** · 03-10-2011, 01:06 AM

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.

**DDR** · 03-11-2011, 02:02 AM

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.

**6StringJazzer** · 03-11-2011, 07:38 AM

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.

**DDR** · 03-14-2011, 06:41 AM

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.

**6StringJazzer** · 03-14-2011, 11:48 AM

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.

**DDR** · 03-24-2011, 10:02 PM

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.

**6StringJazzer** · 03-24-2011, 10:07 PM

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.

**DDR** · 03-25-2011, 01:00 AM

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.

**DDR** · 03-25-2011, 06:33 AM

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.

**6StringJazzer** · 04-02-2011, 07:34 AM

Originally Posted by DDR

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.

**6StringJazzer** · 04-02-2011, 07:36 AM

Originally Posted by DDR

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.

**royUK** · 04-02-2011, 07:48 AM

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

**6StringJazzer** · 04-02-2011, 09:58 AM

@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.

Allow text to spill into adjacent cells

LinkBack

Thread Tools

Rate This Thread

Display

Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Re: Allow text to spill into adjacent cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions