+ Reply to Thread
Results 1 to 34 of 34

Extracting word from phrase within column

  1. #1
    Registered User
    Join Date
    03-28-2006
    Posts
    43

    Extracting word from phrase within column

    Hi All,

    I have a column containing phrases. I need to match each word of the phrase that end with letter "d", copy such words and paste onto a new column.

    Do I have to delimit the words by spaces first so I have only 1 word in each cell?

    How can I do it?

  2. #2
    Tim Williams
    Guest

    Re: Extracting word from phrase within column

    You could use a regexp to do this. Search for help on the VBscript RegExp
    object.

    Or just split on spaces and check each word to see if it's like "*d"



    --
    Tim Williams
    Palo Alto, CA


    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi All,
    >
    > I have a column containing phrases. I need to match each word of the
    > phrase that end with letter "d", copy such words and paste onto a new
    > column.
    >
    > Do I have to delimit the words by spaces first so I have only 1 word in
    > each cell?
    >
    > How can I do it?
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  3. #3
    Ron Rosenfeld
    Guest

    Re: Extracting word from phrase within column

    On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS
    <[email protected]> wrote:

    >
    >Hi All,
    >
    >I have a column containing phrases. I need to match each word of the
    >phrase that end with letter "d", copy such words and paste onto a new
    >column.
    >
    >Do I have to delimit the words by spaces first so I have only 1 word in
    >each cell?
    >
    >How can I do it?


    Give some examples of cell contents and expected output.
    Multiple words in each source cell or a single word per cell?

    Also what kind of data size do you have (how many characters per cell; how many
    cells, on average)?
    --ron

  4. #4
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    I have data that filled the whole worksheet, like 65536 rows of data and 2nd worksheet almost filled up to the max too. Cell contents are words, some single word and some are phrases.

    Example:

    Column A
    red apple
    green apple
    green apple with seed
    orange
    pear

    Basically I want to scan EVERY single word in each phrase in each cell and then copy each word that meets my criteria on a new column. So, say I want to look for words that end with "d", my output will be:

    red
    seed


    More details, I will not know how many words does each cell contain, therefore delimiting it might create many columns if for example one of the cells contain a phrase of 10 words.





    Quote Originally Posted by Ron Rosenfeld
    On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS
    <[email protected]> wrote:

    >
    >Hi All,
    >
    >I have a column containing phrases. I need to match each word of the
    >phrase that end with letter "d", copy such words and paste onto a new
    >column.
    >
    >Do I have to delimit the words by spaces first so I have only 1 word in
    >each cell?
    >
    >How can I do it?


    Give some examples of cell contents and expected output.
    Multiple words in each source cell or a single word per cell?

    Also what kind of data size do you have (how many characters per cell; how many
    cells, on average)?
    --ron

  5. #5
    Ron Rosenfeld
    Guest

    Re: Extracting word from phrase within column

    On Tue, 28 Mar 2006 21:12:38 -0600, KH_GS
    <[email protected]> wrote:

    >
    >I have data that filled the whole worksheet, like 65536 rows of data and
    >2nd worksheet almost filled up to the max too. Cell contents are words,
    >some single word and some are phrases.
    >
    >Example:
    >
    >Column A
    >red apple
    >green apple
    >green apple with seed
    >orange
    >pear
    >
    >Basically I want to scan EVERY single word in each phrase in each cell
    >and then copy each word that meets my criteria on a new column. So, say
    >I want to look for words that end with "d", my output will be:
    >
    >red
    >seed
    >
    >
    >More details, I will not know how many words does each cell contain,
    >therefore delimiting it might create many columns if for example one of
    >the cells contain a phrase of 10 words.
    >
    >



    You can use the VBA Regular expressions module, but I have loaded and installed
    Longre's free morefunc.xll add-in which is simpler for me to implement. It can
    be distributed with any workbook, so you don't have to rely on users to install
    it separately.

    You can download it from http://xcell05.free.fr

    If you don't install the addin (Tools/Addins) then you'll have to register it
    to use it in VBA. See HELP for morefunc for instructions.

    A VBA routine like the following will do what you describe. Should give you
    some ideas to get started:

    ========================
    Option Explicit

    Sub EndWithD()
    Dim c As Range
    Dim output As Range
    Dim wrd As String
    Dim i As Long, o As Long

    Set output = [b1]
    o = -1

    For Each c In Selection
    i = 1
    Do Until i > Run([REGEX.COUNT], c.Text, "\b\w+d\b")
    wrd = Run([regex.mid], c.Text, "\b\w+d\b", i)
    If wrd <> "" Then
    o = o + 1
    output.Offset(o, 0).Value = wrd
    End If
    i = i + 1
    Loop
    Next c
    End Sub
    ====================



    --ron

  6. #6
    Tim Williams
    Guest

    Re: Extracting word from phrase within column

    What if a cell has >1 D word?

    Tim

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I have data that filled the whole worksheet, like 65536 rows of data and
    > 2nd worksheet almost filled up to the max too. Cell contents are words,
    > some single word and some are phrases.
    >
    > Example:
    >
    > Column A
    > red apple
    > green apple
    > green apple with seed
    > orange
    > pear
    >
    > Basically I want to scan EVERY single word in each phrase in each cell
    > and then copy each word that meets my criteria on a new column. So, say
    > I want to look for words that end with "d", my output will be:
    >
    > red
    > seed
    >
    >
    > More details, I will not know how many words does each cell contain,
    > therefore delimiting it might create many columns if for example one of
    > the cells contain a phrase of 10 words.
    >
    >
    >
    >
    >
    > Ron Rosenfeld Wrote:
    >> On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS
    >> <[email protected]> wrote:
    >>
    >> >
    >> >Hi All,
    >> >
    >> >I have a column containing phrases. I need to match each word of the
    >> >phrase that end with letter "d", copy such words and paste onto a new
    >> >column.
    >> >
    >> >Do I have to delimit the words by spaces first so I have only 1 word

    >> in
    >> >each cell?
    >> >
    >> >How can I do it?

    >>
    >> Give some examples of cell contents and expected output.
    >> Multiple words in each source cell or a single word per cell?
    >>
    >> Also what kind of data size do you have (how many characters per cell;
    >> how many
    >> cells, on average)?
    >> --ron

    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:
    > http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  7. #7
    Ron Rosenfeld
    Guest

    Re: Extracting word from phrase within column

    On Tue, 28 Mar 2006 23:22:15 -0500, Ron Rosenfeld <[email protected]>
    wrote:

    >On Tue, 28 Mar 2006 21:12:38 -0600, KH_GS
    ><[email protected]> wrote:
    >
    >>
    >>I have data that filled the whole worksheet, like 65536 rows of data and
    >>2nd worksheet almost filled up to the max too. Cell contents are words,
    >>some single word and some are phrases.
    >>
    >>Example:
    >>
    >>Column A
    >>red apple
    >>green apple
    >>green apple with seed
    >>orange
    >>pear
    >>
    >>Basically I want to scan EVERY single word in each phrase in each cell
    >>and then copy each word that meets my criteria on a new column. So, say
    >>I want to look for words that end with "d", my output will be:
    >>
    >>red
    >>seed
    >>
    >>
    >>More details, I will not know how many words does each cell contain,
    >>therefore delimiting it might create many columns if for example one of
    >>the cells contain a phrase of 10 words.
    >>
    >>

    >
    >
    >You can use the VBA Regular expressions module, but I have loaded and installed
    >Longre's free morefunc.xll add-in which is simpler for me to implement. It can
    >be distributed with any workbook, so you don't have to rely on users to install
    >it separately.
    >
    >You can download it from http://xcell05.free.fr
    >
    >If you don't install the addin (Tools/Addins) then you'll have to register it
    >to use it in VBA. See HELP for morefunc for instructions.
    >
    >A VBA routine like the following will do what you describe. Should give you
    >some ideas to get started:
    >
    >========================
    >Option Explicit
    >
    >Sub EndWithD()
    >Dim c As Range
    >Dim output As Range
    >Dim wrd As String
    >Dim i As Long, o As Long
    >
    >Set output = [b1]
    >o = -1
    >
    >For Each c In Selection
    > i = 1
    > Do Until i > Run([REGEX.COUNT], c.Text, "\b\w+d\b")
    > wrd = Run([regex.mid], c.Text, "\b\w+d\b", i)
    > If wrd <> "" Then
    > o = o + 1
    > output.Offset(o, 0).Value = wrd
    > End If
    > i = i + 1
    > Loop
    > Next c
    >End Sub
    >====================
    >
    >
    >
    >--ron


    Hmmm, the IF...Then is superfluous. It was there in a preliminary version, but
    not required, so should be:

    ==============================
    Option Explicit

    Sub EndWithD()
    Dim c As Range
    Dim output As Range
    Dim wrd As String
    Dim i As Long, o As Long

    Set output = [b1]
    o = -1

    For Each c In Selection
    i = 1
    Do Until i > Run([REGEX.COUNT], c.Text, "\b\w+d\b")
    wrd = Run([regex.mid], c.Text, "\b\w+d\b", i)
    o = o + 1
    output.Offset(o, 0).Value = wrd
    i = i + 1
    Loop
    Next c
    End Sub

    ========================

    --ron

  8. #8
    Ron Rosenfeld
    Guest

    Re: Extracting word from phrase within column

    On Tue, 28 Mar 2006 20:33:32 -0800, "Tim Williams" <timjwilliams at gmail dot
    com> wrote:

    >What if a cell has >1 D word?
    >
    >Tim


    No problem.

    That's the reason for the REGEX.COUNT function.

    Note that the 'i' argument in the REGEX.MID function is for the instance of the
    occurrence.

    Note also that the If...Then in the middle is superfluous. It was present in
    an earlier version, but checking the number of words with the COUNT function
    eliminated the requirement to test the output; as the entire cell will be
    skipped if there is no D word.
    --ron

  9. #9
    Ron Rosenfeld
    Guest

    Re: Extracting word from phrase within column

    On Tue, 28 Mar 2006 23:22:15 -0500, Ron Rosenfeld <[email protected]>
    wrote:

    >On Tue, 28 Mar 2006 21:12:38 -0600, KH_GS
    ><[email protected]> wrote:
    >
    >>
    >>I have data that filled the whole worksheet, like 65536 rows of data and
    >>2nd worksheet almost filled up to the max too. Cell contents are words,
    >>some single word and some are phrases.
    >>
    >>Example:
    >>
    >>Column A
    >>red apple
    >>green apple
    >>green apple with seed
    >>orange
    >>pear
    >>
    >>Basically I want to scan EVERY single word in each phrase in each cell
    >>and then copy each word that meets my criteria on a new column. So, say
    >>I want to look for words that end with "d", my output will be:
    >>
    >>red
    >>seed
    >>
    >>
    >>More details, I will not know how many words does each cell contain,
    >>therefore delimiting it might create many columns if for example one of
    >>the cells contain a phrase of 10 words.
    >>
    >>

    >
    >
    >You can use the VBA Regular expressions module, but I have loaded and installed
    >Longre's free morefunc.xll add-in which is simpler for me to implement. It can
    >be distributed with any workbook, so you don't have to rely on users to install
    >it separately.
    >
    >You can download it from http://xcell05.free.fr
    >
    >If you don't install the addin (Tools/Addins) then you'll have to register it
    >to use it in VBA. See HELP for morefunc for instructions.
    >
    >A VBA routine like the following will do what you describe. Should give you
    >some ideas to get started:
    >
    >========================
    >Option Explicit
    >
    >Sub EndWithD()
    >Dim c As Range
    >Dim output As Range
    >Dim wrd As String
    >Dim i As Long, o As Long
    >
    >Set output = [b1]
    >o = -1
    >
    >For Each c In Selection
    > i = 1
    > Do Until i > Run([REGEX.COUNT], c.Text, "\b\w+d\b")
    > wrd = Run([regex.mid], c.Text, "\b\w+d\b", i)
    > If wrd <> "" Then
    > o = o + 1
    > output.Offset(o, 0).Value = wrd
    > End If
    > i = i + 1
    > Loop
    > Next c
    >End Sub
    >====================
    >
    >
    >
    >--ron


    Hmmm, the IF...Then is superfluous. It was there in a preliminary version, but
    not required, so should be:

    ==============================
    Option Explicit

    Sub EndWithD()
    Dim c As Range
    Dim output As Range
    Dim wrd As String
    Dim i As Long, o As Long

    Set output = [b1]
    o = -1

    For Each c In Selection
    i = 1
    Do Until i > Run([REGEX.COUNT], c.Text, "\b\w+d\b")
    wrd = Run([regex.mid], c.Text, "\b\w+d\b", i)
    o = o + 1
    output.Offset(o, 0).Value = wrd
    i = i + 1
    Loop
    Next c
    End Sub

    ========================

    One other warning: the routine above is case sensitive. If you want it to be
    case insensitive, that's a minor change.
    --ron

  10. #10
    Tim Williams
    Guest

    Re: Extracting word from phrase within column

    You will have to adjust this to however you want to handle multiple matches
    in one piece of text.
    Note if you adjust the regex pattern you need to recompile since the object
    is static.

    Performance is OK: ~2 sec for 20k random strings in my testing.
    If you turn off screenupdating and calculation it might help a bit.


    Tim.


    '******************************************
    Sub Extract()
    Dim matches
    Dim i As Integer
    Dim rngText As Range

    Set rngText = ActiveSheet.Range("A1")

    Do While rngText.Value <> ""

    Set matches = GetDwords(rngText.Value)
    If Not matches Is Nothing Then
    For i = 0 To matches.Count - 1
    rngText.Offset(0, i + 1).Value = matches(i)
    Next i
    End If

    Set rngText = rngText.Offset(1, 0)
    Loop

    End Sub


    Function GetDwords(val) As Object

    Static regEx As Object
    Dim m
    Dim i As Integer

    If regEx Is Nothing Then
    Set regEx = CreateObject("vbscript.regexp")
    regEx.Pattern = "\b(\w+d)\b"
    regEx.Global = True
    regEx.IgnoreCase = True
    End If

    Set m = regEx.Execute(val)
    If m.Count > 0 Then
    Set GetDwords = m
    Else
    Set GetDwords = Nothing
    End If

    End Function
    '**************************************



    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I have data that filled the whole worksheet, like 65536 rows of data and
    > 2nd worksheet almost filled up to the max too. Cell contents are words,
    > some single word and some are phrases.
    >
    > Example:
    >
    > Column A
    > red apple
    > green apple
    > green apple with seed
    > orange
    > pear
    >
    > Basically I want to scan EVERY single word in each phrase in each cell
    > and then copy each word that meets my criteria on a new column. So, say
    > I want to look for words that end with "d", my output will be:
    >
    > red
    > seed
    >
    >
    > More details, I will not know how many words does each cell contain,
    > therefore delimiting it might create many columns if for example one of
    > the cells contain a phrase of 10 words.
    >
    >
    >
    >
    >
    > Ron Rosenfeld Wrote:
    >> On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS
    >> <[email protected]> wrote:
    >>
    >> >
    >> >Hi All,
    >> >
    >> >I have a column containing phrases. I need to match each word of the
    >> >phrase that end with letter "d", copy such words and paste onto a new
    >> >column.
    >> >
    >> >Do I have to delimit the words by spaces first so I have only 1 word

    >> in
    >> >each cell?
    >> >
    >> >How can I do it?

    >>
    >> Give some examples of cell contents and expected output.
    >> Multiple words in each source cell or a single word per cell?
    >>
    >> Also what kind of data size do you have (how many characters per cell;
    >> how many
    >> cells, on average)?
    >> --ron

    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:
    > http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  11. #11
    Tim Williams
    Guest

    Re: Extracting word from phrase within column

    Sorry Ron - my question was for the OP. It was unclear how this was to be
    handled.
    I was a little behind you with my regex post: yours is much more concise...

    Cheers
    Tim

    "Ron Rosenfeld" <[email protected]> wrote in message
    news:[email protected]...
    > On Tue, 28 Mar 2006 20:33:32 -0800, "Tim Williams" <timjwilliams at gmail
    > dot
    > com> wrote:
    >
    >>What if a cell has >1 D word?
    >>
    >>Tim

    >
    > No problem.
    >
    > That's the reason for the REGEX.COUNT function.
    >
    > Note that the 'i' argument in the REGEX.MID function is for the instance
    > of the
    > occurrence.
    >
    > Note also that the If...Then in the middle is superfluous. It was
    > present in
    > an earlier version, but checking the number of words with the COUNT
    > function
    > eliminated the requirement to test the output; as the entire cell will be
    > skipped if there is no D word.
    > --ron




  12. #12
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    Private Sub CommandButton1_Click()
    Dim Cell As Range
    Dim Words As Variant
    Dim i As Long

    For Each Cell In Range(Range("A1"), Range("A1").End(xlDown))
    Words = Split(Cell.Value, " ")
    For i = 0 To UBound(Words)
    If Right(Words(i), 1) = "d" Then
    Debug.Print Words(i)
    'Or do what you want with the word
    End If
    Next
    Next
    End Sub

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I have data that filled the whole worksheet, like 65536 rows of data and
    > 2nd worksheet almost filled up to the max too. Cell contents are words,
    > some single word and some are phrases.
    >
    > Example:
    >
    > Column A
    > red apple
    > green apple
    > green apple with seed
    > orange
    > pear
    >
    > Basically I want to scan EVERY single word in each phrase in each cell
    > and then copy each word that meets my criteria on a new column. So, say
    > I want to look for words that end with "d", my output will be:
    >
    > red
    > seed
    >
    >
    > More details, I will not know how many words does each cell contain,
    > therefore delimiting it might create many columns if for example one of
    > the cells contain a phrase of 10 words.
    >
    >
    >
    >
    >
    > Ron Rosenfeld Wrote:
    > > On Tue, 28 Mar 2006 20:20:28 -0600, KH_GS
    > > <[email protected]> wrote:
    > >
    > > >
    > > >Hi All,
    > > >
    > > >I have a column containing phrases. I need to match each word of the
    > > >phrase that end with letter "d", copy such words and paste onto a new
    > > >column.
    > > >
    > > >Do I have to delimit the words by spaces first so I have only 1 word

    > > in
    > > >each cell?
    > > >
    > > >How can I do it?

    > >
    > > Give some examples of cell contents and expected output.
    > > Multiple words in each source cell or a single word per cell?
    > >
    > > Also what kind of data size do you have (how many characters per cell;
    > > how many
    > > cells, on average)?
    > > --ron

    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  13. #13
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    Hi NickHK

    Thanks for the input! That about catches the whole thing!

    I just added an input value line to list out the words:

    ActiveSheet.Cells(x, y).Value = Words(i)
    x = x + 1


    Why do u make it private though?

  14. #14
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    Private: The stub that Excel generates for a command button.
    But probably better to make it a public function, passing in the range to
    search, letter to find and an array to fill with matching words, returning
    the number of words found. Then dump the array to the desired location.

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi NickHK
    >
    > Thanks for the input! That about catches the whole thing!
    >
    > I just added an input value line to list out the words:
    >
    > ActiveSheet.Cells(x, y).Value = Words(i)
    > x = x + 1
    >
    >
    > Why do u make it private though?
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  15. #15
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    hi NickHK

    I tried changing the if statement to:

    If Left(Words(i), 3) = Left(Words(i + 1), 3) Then

    Theres a runtime error 9, out of range.
    I want to try comparing cell content instead of matching with my specified word.


    Also, how do I change this line

    For Each Cell In Range(Range("A2"), Range("A2").End(xlDown))

    to something more dynamic, where the range begins at the cell i click on before running the macro.


    Please Login or Register  to view this content.

  16. #16
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    That's because you are trying to access an element of the array beyond its
    UBound, which obviously does not exist.
    i.e. If i refers to the last element in the array, then i+1 will beyond the
    array's bound.
    So you would need some check to make sure i<UBound(Words()), then i+1 cannot
    be >UBound(Words()).

    Assuming you want it from the selected cell to the end of the list:
    For Each Cell In Range(Selection, Selection.End(xlDown))

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > hi NickHK
    >
    > I tried changing the if statement to:
    >
    > If Left(Words(i), 3) = Left(Words(i + 1), 3) Then
    >
    > Theres a runtime error 9, out of range.
    > I want to try comparing cell content instead of matching with my
    > specified word.
    >
    >
    > Also, how do I change this line
    >
    > For Each Cell In Range(Range("A2"), Range("A2").End(xlDown))
    >
    > to something more dynamic, where the range begins at the cell i click
    > on before running the macro.
    >
    >
    >
    > Code:
    > --------------------
    > Private Sub CommandButton1_Click()
    > Dim Cell As Range
    > Dim Words As Variant
    > Dim i As Long
    >
    > For Each Cell In Range(Range("A1"), Range("A1").End(xlDown))
    > Words = Split(Cell.Value, " ")
    > For i = 0 To UBound(Words)
    > If Right(Words(i), 1) = "d" Then
    > Debug.Print Words(i)
    > 'Or do what you want with the word
    > End If
    > Next
    > Next
    > End Sub
    > --------------------
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  17. #17
    Tim Williams
    Guest

    Re: Extracting word from phrase within column

    KH_GS

    To satisfy our (my) curiosity, can you let us know why you're doing
    this?
    Seems like a v. large amount of data you are analyzing...

    Thanks
    Tim


  18. #18
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    Part of a project, this stage is something about catching word in different tenses.

    Any idea to go about it?



    Quote Originally Posted by Tim Williams
    KH_GS

    To satisfy our (my) curiosity, can you let us know why you're doing
    this?
    Seems like a v. large amount of data you are analyzing...

    Thanks
    Tim

  19. #19
    Tim Williams
    Guest

    Re: Extracting word from phrase within column

    Not my field at all, but wouldn't you be looking for words ending in "ed"
    rather than just "d" ? Still, neither would distinguish "go/went",
    "come/came" and so on. You might want to implement some kind of lookup for
    those kinds of cases.

    Also, if you're going to be processing that much data you're probably better
    off just reading directly from a text file rather than cramming it all into
    Excel.

    --
    Tim Williams
    Palo Alto, CA


    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Part of a project, this stage is something about catching word in
    > different tenses.
    >
    > Any idea to go about it?
    >
    >
    >
    > Tim Williams Wrote:
    > > KH_GS
    > >
    > > To satisfy our (my) curiosity, can you let us know why you're doing
    > > this?
    > > Seems like a v. large amount of data you are analyzing...
    > >
    > > Thanks
    > > Tim

    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  20. #20
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    If this is more to do with grammar than specific words/letters, I suspect
    there are better ways.
    An easy way would automate Word's spelling/grammar checker.
    Or there are a lot of grammar components out there.

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Part of a project, this stage is something about catching word in
    > different tenses.
    >
    > Any idea to go about it?
    >
    >
    >
    > Tim Williams Wrote:
    > > KH_GS
    > >
    > > To satisfy our (my) curiosity, can you let us know why you're doing
    > > this?
    > > Seems like a v. large amount of data you are analyzing...
    > >
    > > Thanks
    > > Tim

    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  21. #21
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    And as Tim says, Excel may not be the best method.
    Databases (e.g. the free MySQL) that support full text search on BLOBs would
    probably prove more efficient on large amounts of text.
    I think you approach will need to more complex to retrieve meaningful
    results. For example, how you classify "closed" in "..a closed window.." ?
    or "..will be closed.." i.e. future not past.

    NickHK

    "NickHK" <[email protected]> wrote in message
    news:%[email protected]...
    > KH_GS,
    > If this is more to do with grammar than specific words/letters, I suspect
    > there are better ways.
    > An easy way would automate Word's spelling/grammar checker.
    > Or there are a lot of grammar components out there.
    >
    > NickHK
    >
    > "KH_GS" <[email protected]> wrote in
    > message news:[email protected]...
    > >
    > > Part of a project, this stage is something about catching word in
    > > different tenses.
    > >
    > > Any idea to go about it?
    > >
    > >
    > >
    > > Tim Williams Wrote:
    > > > KH_GS
    > > >
    > > > To satisfy our (my) curiosity, can you let us know why you're doing
    > > > this?
    > > > Seems like a v. large amount of data you are analyzing...
    > > >
    > > > Thanks
    > > > Tim

    > >
    > >
    > > --
    > > KH_GS
    > > ------------------------------------------------------------------------
    > > KH_GS's Profile:

    > http://www.excelforum.com/member.php...o&userid=32920
    > > View this thread:

    http://www.excelforum.com/showthread...hreadid=527411
    > >

    >
    >




  22. #22
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    Thanks for the suggestion.

    The data on hand is presented to me in Excel. Furthermore, there are other data tag to each line of word/phrase. This portion is only preliminary, with "d" or "ed" not that crucial at this moment as this require a minor alteration of the code when necessary.

    It is not necessary to analyze it as a phrase, just individual words. This might be further developed. Current objective is to generate list of words with similar spelling, perhaps by first 3 letters or last 3 etc, and to be fine tuned, with inputs of your suggestions.

    By the way should I macro a filter for removing cells that contain numbers only or just a manual action should do it?

  23. #23
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS
    "Current objective" always expand.
    I feel you would make you life more easy for the future, by starting on
    Regular Expressions, as your criteria and/or requirements become more
    complex.
    If you use a database, which is optomised for these processes, it would be
    better, but Excel can handle this.

    You could add a check:
    For Each Cell In Range(Range("A1"), Range("A1").End(xlDown))
    If IsNumeric(Cell.Value) Then
    'Do something here
    Else
    'Continue as before
    Words = Split(Cell.Value, " ").....

    If you are going to delete the rows that contain numeric values, then you
    should work from bottom to top. If you just ignore them, then it does not
    matter.

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks for the suggestion.
    >
    > The data on hand is presented to me in Excel. Furthermore, there are
    > other data tag to each line of word/phrase. This portion is only
    > preliminary, with "d" or "ed" not that crucial at this moment as this
    > require a minor alteration of the code when necessary.
    >
    > It is not necessary to analyze it as a phrase, just individual words.
    > This might be further developed. Current objective is to generate list
    > of words with similar spelling, perhaps by first 3 letters or last 3
    > etc, and to be fine tuned, with inputs of your suggestions.
    >
    > By the way should I macro a filter for removing cells that contain
    > numbers only or just a manual action should do it?
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  24. #24
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    Hi NickHK

    You r right on that, there is indeed further plans of expansion

    Regarding the code that you input earlier, I need some help.

    Column A Column B
    apple green and red 1
    apple blue and green 2
    red green blue 3

    Output:
    apple 1
    green 1
    and 1
    red 1
    apple 2
    blue 2
    and 2
    green 2
    red 3
    green 3
    blue 3

    How can I input value of column B to a column beside the row of words that will be generated by the code below?




    Please Login or Register  to view this content.

  25. #25
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    Hi NickHK

    You r right on that, there is indeed further plans of expansion

    Regarding the code that you input earlier, I need some help.

    Column A Column B
    apple green and red 1
    apple blue and green 2
    red green blue 3

    Output:
    apple 1
    green 1
    and 1
    red 1
    apple 2
    blue 2
    and 2
    green 2
    red 3
    green 3
    blue 3

    How can I input value of column B to a column beside the row of words that will be generated by the code below?




    Please Login or Register  to view this content.

  26. #26
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    Add a line under:
    ActiveSheet.Cells(x, y).Value = Words(i)
    ActiveSheet.Cells(x, y+1).Value=Cell.Offset(0,1).Value
    or if you want the absolute row number
    ActiveSheet.Cells(x, y+1).Value=Cell.Row

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi NickHK
    >
    > You r right on that, there is indeed further plans of expansion
    >
    >
    > Regarding the code that you input earlier, I need some help.
    >
    > Column A Column B
    > apple green and red 1
    > apple blue and green 2
    > red green blue 3
    >
    > Output:
    > apple 1
    > green 1
    > and 1
    > red 1
    > apple 2
    > blue 2
    > and 2
    > green 2
    > red 3
    > green 3
    > blue 3
    >
    > How can I input value of column B to a column beside the row of words
    > that will be generated by the code below?
    >
    >
    >
    >
    >
    > Code:
    > --------------------
    > Sub PrintWords()
    > Dim Cell As Range
    > Dim Words As Variant
    > Dim i As Long
    >
    > x = ActiveCell.Row
    > y = ActiveCell.Column
    >
    > 'For Each Cell In Range(Selection, Selection.End(xlDown))
    > For Each Cell In Range(Range("A2"), Range("A2").End(xlDown))
    > Words = Split(Cell.Value, " ")
    > For i = 0 To UBound(Words)
    > Debug.Print Words(i)
    > 'Or do what you want with the word
    >
    > ActiveSheet.Cells(x, y).Value = Words(i)
    >
    > x = x + 1
    >
    >
    > Next
    > Next
    >
    > End Sub
    > --------------------
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  27. #27
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    You want to use "LIKE" not "=".
    Also, your pattern is the wrong way around > "?d".

    If this is the direction of your project, it really would be a good idea to
    read up on Regular Expressions.

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hi NickHK
    >
    > Thanks for the help once again.
    >
    >
    > I suppose there is something wrong with this line of code as it didn't
    > seem to work:
    >
    > If Right(Words(i), 2) = "d?"
    >
    > does the ? mark make it wildcard or exact match?
    >
    > I had tried "d'" d with single quotation mark, it didn't catch too.
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  28. #28
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    No I wanted to get the word that ends with d as the second last letter.
    Last edited by KH_GS; 03-30-2006 at 11:10 PM.

  29. #29
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    OK.

    Hint: You get that error when the block separators (or whatever they are
    collectively called) do not match.
    These are:
    For Each...Next
    Do...While/Loop
    If.. Then..End If

    NickHK

    "KH_GS" <[email protected]> wrote in message
    news:[email protected]...
    >
    > No I wanted to get the word that ends with d as the second last letter.
    >
    >
    >
    > As for this code below, I get the error "next without for". Totally
    > stumped.
    >
    >
    >
    > Code:
    > --------------------
    > Sub Match3Letters()
    >
    >
    > Application.DisplayAlerts = False
    > Application.ScreenUpdating = False
    >
    > x = ActiveCell.Row
    > y = ActiveCell.Column
    >
    >
    > For Each Cell In Range(Selection, Selection.End(xlDown))
    >
    > If Left(Cells(x, y), 3) = Left(Cells(x + 1, y), 3) Then
    >
    > ActiveSheet.Cells(x, y + 3).Value = Cells(x, y)
    > ActiveSheet.Cells(x, y + 4).Value = Cell.Offset(0, 1).Value
    > ActiveSheet.Cells(x + 1, y + 3).Value = Cells(x + 1, y)
    > ActiveSheet.Cells(x + 1, y + 4).Value = Cell.Offset(1, 1).Value
    > x = x + 1
    >
    > Next
    >
    >
    > Application.DisplayAlerts = True
    > Application.ScreenUpdating = True
    >
    >
    >
    > End Sub
    >
    >
    >
    >
    > --------------------
    >
    >
    >
    >
    >
    > NickHK Wrote:
    > > KH_GS,
    > > You want to use "LIKE" not "=".
    > > Also, your pattern is the wrong way around > "?d".
    > >
    > > If this is the direction of your project, it really would be a good
    > > idea to
    > > read up on Regular Expressions.
    > >
    > > NickHK
    > >
    > > "KH_GS" <[email protected]> wrote in
    > > message news:[email protected]...
    > > >
    > > > Hi NickHK
    > > >
    > > > Thanks for the help once again.
    > > >
    > > >
    > > > I suppose there is something wrong with this line of code as it

    > > didn't
    > > > seem to work:
    > > >
    > > > If Right(Words(i), 2) = "d?"
    > > >
    > > > does the ? mark make it wildcard or exact match?
    > > >
    > > > I had tried "d'" d with single quotation mark, it didn't catch too.
    > > >
    > > >
    > > > --
    > > > KH_GS
    > > >

    > > ------------------------------------------------------------------------
    > > > KH_GS's Profile:

    > > http://www.excelforum.com/member.php...o&userid=32920
    > > > View this thread:

    > > http://www.excelforum.com/showthread...hreadid=527411
    > > >

    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  30. #30
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    yup i realised shortly after i posted that

  31. #31
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    This does not work. I made a mistake again. Will you please correct me?
    Range(Selection, Selection.End(xlLastCell))

    I want the selection to be equivalent to ctrl + shift + end from cell A2.
    and then run the code for each cell like this.
    For Each Cell In Range(Selection, Selection.End(xlDown))

  32. #32
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    Same but change Selection to Range("A2").
    Voila.

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > This does not work. I made a mistake again. Will you please correct me?
    > Range(Selection, Selection.End(xlLastCell))
    >
    > I want the selection to be equivalent to ctrl + shift + end from cell
    > A2.
    > and then run the code for each cell like this.
    > For Each Cell In Range(Selection, Selection.End(xlDown))
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




  33. #33
    Registered User
    Join Date
    03-28-2006
    Posts
    43
    A little divert from the previous, checking the whole phrase in a cell for a match. I want to match words ending with "ing" and print the value of the whole cell containing such match in a new column.

    Data:
    apple running man
    red apple
    burning fire

    output:
    apple running man
    burning fire


    This code is not working
    Please Login or Register  to view this content.

  34. #34
    NickHK
    Guest

    Re: Extracting word from phrase within column

    KH_GS,
    What about:
    If InStr(myString,"ing ")>) Then

    NickHK

    "KH_GS" <[email protected]> wrote in
    message news:[email protected]...
    >
    > A little divert from the previous, checking the whole phrase in a cell
    > for a match. I want to match words ending with "ing" and print the
    > value of the whole cell containing such match in a new column.
    >
    > Data:
    > apple running man
    > red apple
    > burning fire
    >
    > output:
    > apple running man
    > burning fire
    >
    >
    > This code is not working
    >
    > Code:
    > --------------------
    > Sub PrintEnd_ING()
    > Dim Cell As Range
    > Dim myString As String
    >
    > Application.DisplayAlerts = False
    > Application.ScreenUpdating = False
    >
    > x = ActiveCell.Row
    > y = ActiveCell.Column
    >
    > For Each Cell In Range(Selection, Selection.End(xlDown))
    >
    > myString = Cells(x, y).Value
    > If myString Like "*ing " Or myString = "*ing? " Then
    >
    > ActiveSheet.Cells(x, y + 3).Value = myString
    > ActiveSheet.Cells(x, y + 4).Value = Cell.Offset(0, 1).Value
    > x = x + 1
    >
    > End If
    > Next
    >
    > Application.DisplayAlerts = True
    > Application.ScreenUpdating = True
    >
    > End Sub
    >
    >
    >
    >
    > --------------------
    >
    >
    > --
    > KH_GS
    > ------------------------------------------------------------------------
    > KH_GS's Profile:

    http://www.excelforum.com/member.php...o&userid=32920
    > View this thread: http://www.excelforum.com/showthread...hreadid=527411
    >




+ 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