Split text entered in UserForm text box into two cells; split at space within sentence.

Hello All. This is my first post here. I hope my question/request is clear. Please let me know if more information is required.

I have a UserForm to enter text (complete sentences) that needs to be subsequently split into two cells in my workbook. The UserForm textbox has a limit of 104 characters and wordwrap is enforced. I need to "split" the text where the the wrap occurs; at the end of the top line. Splitting a word is not permitted.


I've attached a sample of what I have so far. The VBA includes more functions than addressed in this question. Feel free to either address using the spreadsheet I've attached or as a standalone entry.

Attached: "Alerts Form" command button will show the UserForm. I need the two lines of text to be split into A6 and A7. As it is right now, all text entered into the userform is dumped into A6.

I think one of the "Clear" buttons is not working in this sample. The "Insert" buttons will be removed once this is figured out.

Thanks in advance.

Thanks for quick response. I understand the issue as you've detailed. Let me propose a different method and hope there is code to do it

Same properties for the textbox; 104 max characters, word wrap (although word wrap may not be required).


Sample:
"This text will be entered in my textbox. I'd like for the split to occur after the space following like"


Is there code to do as follows:
Check character 53 of text string. If 53 is a space, split beginning with character 54 into a second cell.
If 53 is not a space...
check character 52 of text string. If 52 is a space, split beginning with character 53 into a second cell.
If 52 is not a space..
check character 51 of text string. If 51 is a space, split beginning with character 52 into a second cell.
and so on....

I would probably want to look back as far 20 characters from the beginning point of position 53.

Hope that makes sense. Thanks in advance.

That works perfectly. Great second option without the carriage return present.

Thanks.