VBA to find and highlight a word in a text string.

[fvisions]

I am looking to create a VBA to find a select word in a text string in col F (such as; "A/L", or "AREA", or "AL") and if found add in col 3 the word "AREA" I tried finding others in this website and review their solutions but none appear to be working for me. This is a sample of what I am looking at. Description is column F, I used bold font to show you this report doesn't bold anything. If I can have the word "AREA" in column E then I can filter the report quickly. Can the experts here help me?

AREA Requests	Description
	LGS RSC 87
	AL request
	A/L RSC 87
	list of outages RSC 83
	89-Raymond-AL inquiry
	empty building
	A/L CONTRACT RSC 86
	IGS Demand Inquiry RSC 85
	Adding A/L Contract
	RATE REVIEW
	Review Deposit
	MANUAL RATE REVIEW REQUEST
	Rate Change
	RATE CHANGE

 

[Akuini]

I think you should go with VBA & use regular expression and utilize "word boundary".
But then this part confused me:

And one day someone could put A_space_L "A L", I have to be flexible if ya know what I means.

 

[fvisions]

yes there would be a space, example of the current report. I am sorry I dont think I am explaining things well

 

[Fluff]

Except one your samples is 89-Raymond-AL inquiry which does not have a space either side of the AL.
As Akuini has mentioned you are probably better off using RegEx, but that is not something I know much about, so not sure if it would be possible.

 

[fvisions]

I think you should go with VBA & use regular expression and utilize "word boundary".
But then this part confused me:

I think this is has been what I am looking for. I am not worried about the one offs like the AL with out a space or with a space. If I can search on a few general common words like a/l & AL this would cut down on a lot of work. How do I go about doing a "word boundary" vba?

 

[Fluff]

That's something to do with RegEx, which I can't help with so hopefully @Akuini or another member will step in.

 

[Akuini]

Try this, (note: I put the keywords in col A)
The code will match the keywords as a whole word so as you can see "AL" in "MANUAL" is ignored because "AL" is just a part of another word.
But I don't know how to do this part:
if install then Install in col E
why install must be inserted in col E as install? because it has "AL" in it?

Sub a1157686a()
'https://www.mrexcel.com/board/threads/vba-to-find-and-highlight-a-word-in-a-text-string.1157686/#post-5615160
Dim i As Long
Dim va
Dim regEx As Object
Dim ax As Range

           Set regEx = CreateObject("VBScript.RegExp")
           With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = True
            End With
    
va = Range("F1", Cells(Rows.Count, "F").End(xlUp))
    
For i = 1 To UBound(va, 1)
   For Each ax In Range("A1", Cells(Rows.Count, "A").End(xlUp))
        regEx.Pattern = "\b" & ax & "\b"
        If regEx.Test(va(i, 1)) Then
            Cells(i, 5) = "AREA"
        End If
    Next
Next
End Sub

RESULT:

AL					Description
A/L					LGS RSC 87
AREA				AREA	AL request
				AREA	A/L RSC 87
					list of outages RSC 83
				AREA	89-Raymond-AL inquiry
					empty building
				AREA	A/L CONTRACT RSC 86
					IGS Demand Inquiry RSC 85
				AREA	Adding A/L Contract
					RATE REVIEW
					Review Deposit
					MANUAL RATE REVIEW REQUEST
					Rate Change
					RATE CHANGE
				AREA	SOME AREA
					SOME AREAS

 

[fvisions]

Thank you this works great

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.