Extracting word from phrase within column

[NickHK]

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

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]

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?

 

[NickHK]

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]

Hi NickHK

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


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 word
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 Su

 

[KH_GS]

Hi NickHK

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


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 word
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 Su

 

[NickHK]

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]

Hi NickHK

Thanks for the help once again.


I suppose there is something wrong with this line of code as it didn'
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

 

[NickHK]

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]

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". Totall
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]

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]

yup i realised shortly after i posted that

 

[KH_GS]

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))

 

[NickHK]

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

NickHK

 

[KH_GS]

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

 

[NickHK]

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

NickHK