Split a Column into 2, use a character limit that does not cut words in half.

[vishesh10667]

Title says it all.

I have huge columns of data that can only be 30 characters long. I want to use a formula to split the column into two. The original column will have a character limit of 30, and should not cut words in half. If the Length will be more than 30 while including the entire last word of the cell, that word should be the first displayed in adjacent cell in the 2nd column.

The 2nd column should also be 30 characters long. And rest in 3 column


I usually use a Text to Columns method, but dont know how to make it not cut off the words. Any ideas?

Thanks in advance

 

[KOKOSEK]

Looks okay to me.

weird.

 

[Peter_SSs]

Thanks for the coding but it picks up only 30 rows in a sheet

The code should work on every cell in column A, starting at A2 and ending at the last row with data in column A.
Note though that the code was copied from an old post of mine and may not do exactly what you want. It will break the text into the block size given but if the text is long enough it could split it into more than 3 columns. Post back if that is a problem and you want to use a vba method.

 

[Peter_SSs]

this formula is not working on my excel. It just got paste as it is. It doesn't picking the value

That sounds like the cell that you put it into is formatted as Text. If that is so then format the cell as General and the re-confirm the formula by selecting that cell, pressing F2 and then Enter

 

[vishesh10667]

That sounds like the cell that you put it into is formatted as Text. If that is so then format the cell as General and the re-confirm the formula by selecting that cell, pressing F2 and then Enter

Yes i have tried it but it is highlighting the Len formula given at the last

 

[Peter_SSs]

weird.

Perhaps you didn't adjust my formula correctly? Try a blank worksheet and use this icon

to copy my data and formulas to A1 in that blank sheet

 

[vishesh10667]

The code should work on every cell in column A, starting at A2 and ending at the last row with data in column A.
Note though that the code was copied from an old post of mine and may not do exactly what you want. It will break the text into the block size given but if the text is long enough it could split it into more than 3 columns. Post back if that is a problem and you want to use a vba method.

While running the macros it showing a runtime error. which is attached herewith

 

[Peter_SSs]

Yes i have tried it but it is highlighting the Len formula given at the last

For a start, try what I suggested in post 16

 

[Peter_SSs]

While running the macros it showing a runtime error. which is attached herewith

Hard to read much in that image but it looks like the code has processed some of the rows. Can you post the text of what is in the next row in column A (looks like that might be row 9?) as I assume there must be something different/unusual about what is in that cell.

 

[KOKOSEK]

Perhaps you didn't adjust my formula correctly?

It's ok. My bad. Forget change 1 reference.

 

[vishesh10667]

It's ok. My bad. Forget change 1 reference.

can you send me the revised ?