Distribute contents of a cell into other cells according to number of characters

Hi, I wonder if any one can figure out a formula to to the following:

The users enter terms separated by spaces into a single cell. Then I have to later make sure that when those terms go over 50 characters, that I distribute them into as many as a total of three cells, with a maximum of 50 characters each.

So I wanted to build a formula that would automate my task, or at least help a little bit. The perfect formula would do this:

Say the text is on A1: cold jacket plaid pockets zipper hood cotton leather outwear fashion woolly silky designer comfy trendy casual smart (a total of 116 characters)


I have to spread this into B1, C1 and D1 and not go over 50 characters in each of them. So my formula would go like

On B1 = A1 up to the last space before reaching character #50 (cold jacket plaid pockets zipper hood cotton )
On C1 = A1 starting to count on first word after last on B1 up to the last space before reaching character count 50 (leather outwear fashion woolly silky designer )
On D1 = The remaining words: comfy trendy casual smart

The users have a limit of 150 with data validation (giving them the three columns is not an option though)

What I do now is really pitiless: I put a LEN formula on B1 to get an idea of how many cells I need, then just manually cut and paste the text many, many, many times a day

Thank you for any ideas!

Marcia

Hello,

what you need a the MID function:

In cell B1:

Formula:

=MID($A1,(COLUMN(A1)*50+1)-50,50)

Once you input the first formula you just grab the handles and drag as many cells to the right as you want. In this case up to D1

Thanks

at B1
=MID($A1,COLUMNS($A:A)*50-49,50)
copy to right

Thank you all! I was not familiar with the MID function and am really glad I learned it now.

And the macro is perfect!

Very helpful, thank you again.