a better way to do text to columns with vba

[monsierexcel]

hi guys if i have a big text string is there a function or vba code to break this down into say 5 rows with equal strings
for example

string 1:" The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog The quick brown fox jumps over the lazy dog"

after vba:
row 1 col A: The quick brown fox jumps over the lazy dog
row 2 col A: The quick brown fox jumps over the lazy dog
row 3 col A: The quick brown fox jumps over the lazy dog
row 4 col A: The quick brown fox jumps over the lazy dog
row 5 col A: The quick brown fox jumps over the lazy dog

thank you guys
[h=1][/h]

 

[Joe4]

You could do formulas that take the length of the total string, and then divide it up into 5 equal substring, using the LEFT, RIGHT, and MID functions.
The only potential issue there is it might cut off some words in the middle.
If that is an issue, then you might need a more elaborate VBA solution.

 

[MickG]

For data in "A1", Result start "B1".
This code works for your data, but I expect could show some peculiar results depending on data.

[COLOR="Navy"]Sub[/COLOR] MG14Feb50
[COLOR="Navy"]Dim[/COLOR] Mystr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Pts [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Mystr = [a1]
Pts = 5
[COLOR="Navy"]For[/COLOR] n = 1 To Len(Mystr)
    [COLOR="Navy"]If[/COLOR] n Mod Len(Mystr) / Pts = 0 Or n = 1 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Cells(c + 1, 1) = Trim(Mid(Mystr, n + 1, Len(Mystr) / Pts))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Joe4]

Mick,

I think you code needs some tweaks. It seems to mess up the first value it returns. It starts at character 2, so it skips the first letter.
Then it shows the same character at the end of line 1 and the beginning of line 2, i.e. this is what it returns when I tested it out.

[TABLE="width: 1245"]
<colgroup><col></colgroup><tbody>[TR]
[TD]he quick brown fox jumps over the lazy dog T[/TD]
[/TR]
[TR]
[TD]The quick brown fox jumps over the lazy dog[/TD]
[/TR]
[TR]
[TD]The quick brown fox jumps over the lazy dog[/TD]
[/TR]
[TR]
[TD]The quick brown fox jumps over the lazy dog[/TD]
[/TR]
[TR]
[TD]The quick brown fox jumps over the lazy dog[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Mick,

Also see that it does some weird things sometimes, like not even return a 5th line.
For example, try running it on the word "octagons" and see what happens.

I am waiting on clarification if it is OK to split words down the middle or not before trying to come up with anything.

I am also curious as to how they would like it split up if the string length is not exactly divisible by 5. For example, if it is 22 characters, what are acceptable split lengths?
You could have something like:
4,4,4,4,6
5,5,4,4,4
etc.

 

[monsierexcel]

hey Joe, yeh thats already been tried, i want it by character length really. eh 50 chars per line to the nearest word.

 

[Joe4]

Take a look here: http://meadinkent.co.uk/xl-split-text.htm