Announcement

Collapse
No announcement yet.

Extract TextBox text into multiple cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract TextBox text into multiple cells



    I have a textbox on one sheet that has multiple paragraphs - blocks of text separated by carriage returns.

    I want some vba code that would, on another sheet, extract the contents of the textbox and put each paragraph in a separate cell.

    For example: Sheet(1) has TextBox1. In this textbox is the following block of text:

    [StartText]
    This is paragraph one with some text in it.

    This is paragraph two with additional text in it.

    And this is paragraph three.
    [/StartText]

    In Sheet(2), I would have the following after running the script:

    Cell B1 = "This is paragraph one with some text in it."
    Cell B2 = "This is paragraph two with additional text in it."
    Cell B3 = "And this is paragraph three."

    All of this would be done in a loop so that the number of actual paragraphs in the textbox is dynamic and I could control the destination cells.

    I've seen some forum entries that show how to extract the entire block of text out of a textbox into a single cell, but not one cell per paragraph (or carriage return).

    Thanks in advance.

    Troy

  • #2
    Re: Extract TextBox text into multiple cells

    Code:
    Sub SplitText()
    
        Dim strStringToSplit As String
        strStringToSplit = ActiveSheet.TextBox1.Text
        Range("B1").Resize(UBound(Split(strStringToSplit, Chr(13))) + 1, 1).Value = Application.Transpose(Split(strStringToSplit, Chr(13)))
        strStringToSplit = vbNullString
        
    End Sub
    Last edited by S M C; September 18th, 2010, 12:01. Reason: Revised Ubound
    Cheers,

    S M C

    Click To Read: How To Use Tags In Your Threads/Posts
    Please take time to read Forum Rules before posting
    Message To Cross Posters

    Comment


    • #3
      Re: Extract TextBox text into multiple cells

      Thank you for the quick reply. This seems to kind of work - what I get is several consecutive cells starting in A1 going through A5. Five cells makes sense since there are 5 CR's in the text block. So far, so good.

      The problem is that each cell is the same text - the first paragraph. Also, I want to be able to control the destination cells mainly because I want to put each paragraph in a series of cells in the same column. This is why I was thinking some kind of loop would be necessary to that I could increment the row index as I went along.

      Any thoughts on this one? Thank you.

      Comment


      • #4
        Re: Extract TextBox text into multiple cells

        Can you try copying the code again, I had made a change in between
        Cheers,

        S M C

        Click To Read: How To Use Tags In Your Threads/Posts
        Please take time to read Forum Rules before posting
        Message To Cross Posters

        Comment


        • #5


          Re: Extract TextBox text into multiple cells

          Awesome! Works as advertised. I don't know what they're paying you, but it's not enough! Thanks so much.

          Troy

          Comment

          Working...
          X