underlining vocab word in text string

[maizen]

Hello,

In need of your expertise once again. I have text from a book on a spread sheet via text to columns. I have vocabulary words set in another column. I would like to have a column next to the vocab word for reference that would contain about 5 words before and after the vocab word with the vocab word bolded and underlined.

for example: vocab word = portion

this column would contain a portion of text with a vocabulary word

Is there a way in excel to do this?

Any input would be greatly appreciated.

 

[mikerickson]

Have you looked at conditional formatting?

 

[maizen]

I thought that might be where I could make it work, but couldn't figure out the right formula. I've used it before on a whole column, but not on individual cells within same column. Any input you have would be appreciated.

 

[mikerickson]

If you could post you data and desired output it would help.

 

[maizen]

OK, I'll give a shortened example so as not to take up too much space.

Text from reading assigment:

It was the best of times, it was the worst of times, it was the age of wisdom, it was the age of foolishness, it was the epoch of belief, it was the epoch of incredulity, it was the season of Light, it was the season of Darkness, it was the spring of hope, it was the winter of despair, we had everything before us, we had nothing before us, we were all going direct to Heaven, we were all going direct the other way - in short, the period was so far like the present period, that some of its noisiest authorities insisted on its being received, for good or for evil, in the superlative degree of comparison only.

Vocabulary words will go in one column:

belief, despair, authorities, superlative...

Next column would contain bold, underlined vocabulary word with 5 words before and after for reference:

it was the winter of despair, we had everything before us,

Hope that helps, thanks for your input.

 

[mikerickson]

If your big text is in A1 and the list of keywords in B3:B6, this might work

Sub test()
    Dim mainTextCell As Range, MainText As String
    Dim keyWordRange As Range
    Dim oneCell As Range, oneKeyword As String
    Dim colOffset As Long
    
    Set mainTextCell = Sheet1.Range("A1")
    Set keyWordRange = Sheet1.Range("B3:B6")
    
    MainText = Application.Trim(CStr(mainTextCell.Value))
    
    MainText = Replace(MainText, ",", vbNullString)
    MainText = Replace(MainText, ";", vbNullString)
    MainText = Replace(MainText, ":", vbNullString)
    MainText = Replace(MainText, ".", vbNullString)
    MainText = Replace(MainText, "!", vbNullString)
    MainText = Replace(MainText, "?", vbNullString)
    MainText = Replace(MainText, "'", vbNullString)
    MainText = Replace(MainText, """", vbNullString)
    MainText = Replace(MainText, ",", vbNullString)
    MainText = Replace(MainText, ",", vbNullString)
    
    For Each oneCell In keyWordRange.Cells
        With oneCell
                colOffset = 0
                oneKeyword = CStr(.Value)
                
                Do
                    colOffset = colOffset + 1
                    With .Offset(0, colOffset)
                        Rem font to normal
                        With .Font
                            .Bold = False
                            .Underline = False
                            .ColorIndex = xlAutomatic
                        End With
                    
                        .Value = WordsSurrounding(MainText, oneKeyword, 5, colOffset)
                        
                        Rem highlight keyword
                        With .Characters(InStr(1, .Value, " " & oneKeyword & " ") + 1, Len(oneKeyword))
                            With .Font
                                .Bold = True
                                .Underline = True
                                .ColorIndex = 3
                            End With
                        End With
                    End With
                Loop Until .Offset(0, colOffset).Value = vbNullString
        End With
    Next oneCell
End Sub

Function WordsSurrounding(ByVal bigText As String, ByVal keyWord As String, ByVal countOfWords As Long, _
                                                Optional Occurance As Long = 1) As String
    Dim splitSentence As Variant
    Dim preceedingWords As String, pWords As Variant
    Dim followingWords As String, fWords As Variant
    Dim i As Long
    
        bigText = Application.Trim(bigText)
        splitSentence = Split(" " & bigText & " ", " " & keyWord & " ")
        
        If Occurance < UBound(splitSentence) + 1 Then
            preceedingWords = Trim(splitSentence(Occurance - 1))
        End If
        If Occurance <= UBound(splitSentence) Then
            followingWords = Trim(splitSentence(Occurance))
        End If
        pWords = Split(preceedingWords, " ")
        fWords = Split(followingWords, " ")
        For i = 0 To UBound(pWords) - countOfWords
            pWords(i) = vbNullString
        Next i
            preceedingWords = Trim(Join(pWords, " "))
        For i = countOfWords To UBound(fWords)
            fWords(i) = vbNullString
        Next i
        followingWords = Trim(Join(fWords, " "))
        If Len(followingWords) + Len(preceedingWords) > 0 Then
        WordsSurrounding = Application.Trim(preceedingWords & " " & keyWord & " " & followingWords)
        End If
End Function

 

[maizen]

looks complicated, I'm a basic excel user, so I'll need more info as to where I'm placing the formulas. I greatly appreciate your help.

 

[mikerickson]

This is VBA. Open the VB editor and put that in a normal code module, then run the macro Test.

The result you desire cannot be gotten with basic excel.

 

[maizen]

Ok, do I just copy and paste the entire string of code that you have listed into the macro?

 

[mikerickson]

Copy paste the entire macro that I posted into the code module.