+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    11-09-2015
    Location
    Melbourne, FL
    MS-Off Ver
    2010 Professional Plus
    Posts
    4

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

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

    If you're talking about visually where the line wraps, and not where a new line occurs with the Enter key / carriage return, then I don't think what you are asking is possible.

    This article seems to confirm.
    To quote:

    Is it possible to somehow parse each line in the text box without using the ENTER key? Well, we can’t say with 100% certainty that it can’t be done. But for all practical purposes, no, it can’t be done.

    Why not? Well, for one thing, suppose you had a text box that resized itself any time the HTA was resized. In that case the value of each “line” would change depending on the width of the text box.
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  3. #3
    Registered User
    Join Date
    11-09-2015
    Location
    Melbourne, FL
    MS-Off Ver
    2010 Professional Plus
    Posts
    4

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

    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.

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

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

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-09-2015
    Location
    Melbourne, FL
    MS-Off Ver
    2010 Professional Plus
    Posts
    4

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

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

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Split text into cells
    By fol2 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2013, 10:57 PM
  2. Replies: 4
    Last Post: 07-25-2013, 05:28 AM
  3. [SOLVED] Unable to split text and numbers into Separate Cells that have no space
    By BDavis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-21-2013, 04:39 PM
  4. How Do I Split Text Based Upon Two Values To Split
    By HowdeeDoodee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2012, 05:05 AM
  5. Split a long text in a userform textbox to multiple rows on a worksheet
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-31-2012, 09:03 AM
  6. Text to columns, split at first space only
    By Wowbagger in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 06-21-2012, 01:27 PM
  7. [SOLVED] text cells end page how split to next. Text lost!
    By Elaine in forum Excel General
    Replies: 1
    Last Post: 08-28-2005, 01:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1