+ Reply to Thread
Results 1 to 6 of 6

Split sentences into words

  1. #1
    Forum Contributor
    Join Date
    11-23-2004
    Location
    Bristol
    MS-Off Ver
    2016
    Posts
    225

    Question Split sentences into words

    Does anybody know of a formula that can look at a sentence of say 3 or 4 words and then look for the spaces inbetween the words and put each word in a different cell underneath each other?

  2. #2
    Andrew Taylor
    Guest

    Re: Split sentences into words

    You can just about do it with worksheet functions, but it's rather
    long-winded (and dependent on correct input)

    With the sentence in A1
    A2: =LEFT(A1,FIND(" ",A1)-1)
    A3: =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)
    A4: "and so on"...

    It's a bit easier if you have some helper cells that just contain the
    positions of the spaces:

    A2: =FIND(" ",$A$1)
    A3: =FIND(" ",$A$1&" ",A2+1) ' append space to avoid special case for
    last word
    and copy the A3 down a few rows
    B2: =LEFT($A$1,A2-1)
    B3 =MID($A$1,A2+1,A3-A2-1)
    and copy B3 down.

    HTH
    Andrew


    sparx wrote:
    > Does anybody know of a formula that can look at a sentence of say 3 or 4
    > words and then look for the spaces inbetween the words and put each word
    > in a different cell underneath each other?
    >
    >
    > --
    > sparx
    > ------------------------------------------------------------------------
    > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
    > View this thread: http://www.excelforum.com/showthread...hreadid=515292



  3. #3
    Kevin B
    Guest

    RE: Split sentences into words

    VBA has a function named SPLIT which does what you want. The syntax for the
    function is SPLIT(String value, Delimiter)

    If you place a sentence in column A row 1 of Sheet 1, and place another in
    column a row 2, you can run the following code to parse the words separated
    by spaces and place them on sheet 2 column A in a column. Hope it helps to
    point you in the general direction.

    Sub SplitSentences()

    Dim wb As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim l As Long
    Dim strSentence As String
    Dim varArray As Variant
    Dim varItems As Variant

    Set wb = ActiveWorkbook
    Set ws1 = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")

    ws1.Activate
    Range("A1").Select
    strSentence = ActiveCell.Value

    Do Until strSentence = ""
    ws2.Activate
    Range("A1").Select
    varArray = Split(strSentence, " ")
    varItems = varArray
    For Each varItems In varArray
    ActiveCell.Offset(l).Value = varItems
    l = l + 1
    Next varItems
    ws1.Activate
    ActiveCell.Offset(1).Select
    strSentence = ActiveCell.Value
    Loop

    Set wb = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing

    --
    Kevin Backmann


    "sparx" wrote:

    >
    > Does anybody know of a formula that can look at a sentence of say 3 or 4
    > words and then look for the spaces inbetween the words and put each word
    > in a different cell underneath each other?
    >
    >
    > --
    > sparx
    > ------------------------------------------------------------------------
    > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
    > View this thread: http://www.excelforum.com/showthread...hreadid=515292
    >
    >


  4. #4
    Ron Rosenfeld
    Guest

    Re: Split sentences into words

    On Wed, 22 Feb 2006 07:38:30 -0600, sparx
    <[email protected]> wrote:

    >
    >Does anybody know of a formula that can look at a sentence of say 3 or 4
    >words and then look for the spaces inbetween the words and put each word
    >in a different cell underneath each other?


    1. Use the Data/Text to Columns wizard with <space> as the delimiter.

    or

    2. Download and install Longre's free morefunc.xll add-in from
    http://xcell05.free.fr

    Then, with your sentence in A1:

    B1: =REGEX.MID($A1,"\w+",COLUMNS($A:A))

    and copy/drag across as far as needed.

    If it is an issue, there is an option to distribute the morefunc.xll add-in
    with the workbook.


    --ron

  5. #5
    Sloth
    Guest

    RE: Split sentences into words

    You will first need to transpose the data to be in one column only.

    Select the column you want to split
    In the "Data" menu, select "Text to Columns"
    Select "Delimited" and then click "Next"
    Check only the box next to Space, and maybe the one next to "Treat
    consecutive delimiters as one" and then click "Finish"

    This will split all cells in the column you selected and push the words to
    the right in other cells as you described you need. You can then tranpose
    the data to get the orientation you want.



    "sparx" wrote:

    >
    > Does anybody know of a formula that can look at a sentence of say 3 or 4
    > words and then look for the spaces inbetween the words and put each word
    > in a different cell underneath each other?
    >
    >
    > --
    > sparx
    > ------------------------------------------------------------------------
    > sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
    > View this thread: http://www.excelforum.com/showthread...hreadid=515292
    >
    >


  6. #6
    Forum Contributor
    Join Date
    11-23-2004
    Location
    Bristol
    MS-Off Ver
    2016
    Posts
    225

    Sentence Spaces

    Thanks for the response - I will try each and will try it in my worksheet.

    Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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