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

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
184
Office Version
  1. 365
Platform
  1. Windows
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 RequestsDescription
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
184
Office Version
  1. 365
Platform
  1. Windows
1609953305197.png
yes there would be a space, example of the current report. I am sorry I dont think I am explaining things well
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 29, 2008
Messages
184
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,682
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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:
ALDescription
A/LLGS RSC 87
AREAAREAAL request
AREAA/L RSC 87
list of outages RSC 83
AREA89-Raymond-AL inquiry
empty building
AREAA/L CONTRACT RSC 86
IGS Demand Inquiry RSC 85
AREAAdding A/L Contract
RATE REVIEW
Review Deposit
MANUAL RATE REVIEW REQUEST
Rate Change
RATE CHANGE
AREASOME AREA
SOME AREAS
 
Solution

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help & thanks for the feedback. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,905
Messages
5,627,572
Members
416,255
Latest member
amethystia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top