Find specific words in a cell containing a conversation

[Mattlynn via OfficeKB.com]

Hi
We have a spreadsheet which is basically a digital speech to text translation
from our phone system of a survey line.
We would like to try and find/pick out/highlight in one way or another
specific words such as Survey or transfer.

Is there a macro or a formula that can best achieve this.
Ideally the results would be flagged in the next available column saying the
word it found.

I have been bashing my head for a while on this. I found one formula but it
didnt work in my 2002 version i am using.

Many Thanks
Matt

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Ron Rosenfeld]

Hi
We have a spreadsheet which is basically a digital speech to text translation
from our phone system of a survey line.
We would like to try and find/pick out/highlight in one way or another
specific words such as Survey or transfer.

Is there a macro or a formula that can best achieve this.
Ideally the results would be flagged in the next available column saying the
word it found.

I have been bashing my head for a while on this. I found one formula but it
didnt work in my 2002 version i am using.

Many Thanks
Matt

Kind of hard to understand exactly what you want. The SEARCH worksheet
function should be able to do what you describe.

But here's a macro that highlights the desired word(s) within the cell, and
also outputs it into the next cell. The macro, as written, only finds, and
highlights, the first instance of any of the words that are in the array
constant. But this behavior could be modified.

It assumes the data to be processed is in A1:A10. Again, this can be modified.

It assumes the cell contents are TEXT, and NOT the result of any formulas.

This should get you started.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

===========================================
Option Explicit
Sub HighlightWords()
Dim aW() As Variant
Dim c As Range, rg As Range
Dim i As Long, j As Long

aW = Array("survey", "transfer")
Set rg = Range("A1:A10") 'or whatever

rg.Font.Color = vbBlack
rg.Offset(0, 1).Clear

For Each c In rg
For i = 0 To UBound(aW)
j = InStr(1, c.Value, aW(i), vbTextCompare)
If j > 0 Then
c.Characters(j, Len(aW(i))).Font.Color = vbRed
c.Offset(0, 1).Value = c.Offset(0, 1).Value & _
IIf(Len(c.Offset(0, 1).Value) > 0, ", ", "") & _
aW(i)
End If
Next i
Next c

End Sub
=============================================
--ron

 

[Mattlynn via OfficeKB.com]

That looks wicked Ron - I will test and let you know.
Thanks everso much for your help mate
Matt

Hi
We have a spreadsheet which is basically a digital speech to text translation

[quoted text clipped - 11 lines]

Many Thanks
Matt

Kind of hard to understand exactly what you want. The SEARCH worksheet
function should be able to do what you describe.

But here's a macro that highlights the desired word(s) within the cell, and
also outputs it into the next cell. The macro, as written, only finds, and
highlights, the first instance of any of the words that are in the array
constant. But this behavior could be modified.

It assumes the data to be processed is in A1:A10. Again, this can be modified.

It assumes the cell contents are TEXT, and NOT the result of any formulas.

This should get you started.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

===========================================
Option Explicit
Sub HighlightWords()
Dim aW() As Variant
Dim c As Range, rg As Range
Dim i As Long, j As Long

aW = Array("survey", "transfer")
Set rg = Range("A1:A10") 'or whatever

rg.Font.Color = vbBlack
rg.Offset(0, 1).Clear

For Each c In rg
For i = 0 To UBound(aW)
j = InStr(1, c.Value, aW(i), vbTextCompare)
If j > 0 Then
c.Characters(j, Len(aW(i))).Font.Color = vbRed
c.Offset(0, 1).Value = c.Offset(0, 1).Value & _
IIf(Len(c.Offset(0, 1).Value) > 0, ", ", "") & _
aW(i)
End If
Next i
Next c

End Sub
=============================================
--ron

 

[Mattlynn via OfficeKB.com]

Worked a Treat - you are fantastic Ron !

Hi
We have a spreadsheet which is basically a digital speech to text translation

[quoted text clipped - 11 lines]

Many Thanks
Matt

Kind of hard to understand exactly what you want. The SEARCH worksheet
function should be able to do what you describe.

But here's a macro that highlights the desired word(s) within the cell, and
also outputs it into the next cell. The macro, as written, only finds, and
highlights, the first instance of any of the words that are in the array
constant. But this behavior could be modified.

It assumes the data to be processed is in A1:A10. Again, this can be modified.

It assumes the cell contents are TEXT, and NOT the result of any formulas.

This should get you started.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

===========================================
Option Explicit
Sub HighlightWords()
Dim aW() As Variant
Dim c As Range, rg As Range
Dim i As Long, j As Long

aW = Array("survey", "transfer")
Set rg = Range("A1:A10") 'or whatever

rg.Font.Color = vbBlack
rg.Offset(0, 1).Clear

For Each c In rg
For i = 0 To UBound(aW)
j = InStr(1, c.Value, aW(i), vbTextCompare)
If j > 0 Then
c.Characters(j, Len(aW(i))).Font.Color = vbRed
c.Offset(0, 1).Value = c.Offset(0, 1).Value & _
IIf(Len(c.Offset(0, 1).Value) > 0, ", ", "") & _
aW(i)
End If
Next i
Next c

End Sub
=============================================
--ron

 

[Ron Rosenfeld]

Worked a Treat - you are fantastic Ron !

Glad to help. Thanks for the feedback.
--ron