Splitting text into 3 columns

[s.b.e]

Hi all,

I have 4 columns with names inside. I need to make sure the all the columns has a maximum of 30 characters only. I would need whole names inside. It cant be cut off half way.

Scenario 1

If the first column has 20 characters inside, 2nd column has 20 characters and 3rd column has 20 characters. It should remain this way.

Scenario 2.

If the first column has 31 characters inside, 2nd column has 15 characters and 3rd column has 15 characters.
The last word of the first column should shift over to the 2nd column. And continue from there. leaving the 3rd column as it is.

Scenario 3

If the first column has 40 characters, 2nd column has 30 characters and 3rd column has 15 characters.
The first column should be at a max of 30 characters. The remainder will be pushed to the start of the 2nd column combining with the original data from the 2nd column. And the now if the 2nd column has more than 30 characters it will push over to the 3rd column.


Example,

A1
1. John Smith Bohdie Alam Shah mane
2. Poer Poemn John
3. Peter Russel

B2
2. As owner and changi hosptial champ
3. Champ

C3
2. Jeremieh


I would need it to look like below.


A1
1. John Smith Bohdie Alam Shah
2. Poer Poemn John
3. Peter Russel

B2
1. Mane
2. As owner and changi hosptial
3. Champ

C3

2. champ Jeremieh


The cells are next to each other. Hence A1,B1,C1
I used the numbering to make it easier to note which row it comes from.


Thank you!!!! for the help. If u guys could provide formulas or codes i would be so grateful. Using excel 2013.

[LeoTaxi]

Try and let me know

Please Login or Register  to view this content.

Kind regards
Leo

[s.b.e]

Hi Leo,

Would it be possible for the range to extend till "E" instead of C

[LeoTaxi]

Sure

Please Login or Register  to view this content.

Kind regards
Leo

[s.b.e]

Hi Leo,

Thank you so much for the help. The code works perfectly.

Best Regards,

S.B.E