a better way to do text to columns with vba

monsierexcel

New Member
Joined
Nov 19, 2018
Messages
29
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]
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
For data in "A1", Result start "B1".
This code works for your data, but I expect could show some peculiar results depending on data.
Code:
[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
 
Upvote 0
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.
Code:
[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]
 
Upvote 0
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.
 
Last edited:
Upvote 0
hey Joe, yeh thats already been tried, i want it by character length really. eh 50 chars per line to the nearest word.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,307
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top