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

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
191
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Would something like

Excel Formula:
=IF(OR(IFERROR(SEARCH("AL";F2);FALSE());IFERROR(SEARCH("A/L";F2);FALSE()));"Area";"")

solve your problem? I see I put it in column C instead of column E as you asked for, but the results would be the same...

1609938323480.png
 
Upvote 0
tried copying code and typing code both ways gives me an error and highlights the "AL" part of the code.
 
Upvote 0
Try changing the semi-colons to commas
 
Upvote 0
new error, entered too few arguments., what I was hoping for was VBA guidance because I also have to do a second search with results also in col E for "SL", STREET", etc = "Street" and another string for Inquiry's, Installs, and Removals. Basically I am trying to, in col e, create a type list to filter or sort on based from col F's messy description
 
Upvote 0
Will the AL always be in capitals & could you have other words that contain AL that should be ignored?
 
Upvote 0
I am planning on lower case as well as upper case. This report is generated from a call center and the operators do not have a standard for this field...yet, so I am looking at a vba that I can add as issues come up for instance today they are all upper case but tomorrow one could appear lower case etc. And one day someone could put A_space_L "A L", I have to be flexible if ya know what I means.
 
Upvote 0
In that case you have a major problem, how would code be able to distinguish an AL that should be an Area & something like Install which shouldn't?
 
Upvote 0
I think I confused this...I am looking for multiple words in the text string from col F - like...AL or Install. If AL then Area in col E, if install then Install in col E
 
Upvote 0
Yes, but the problem is that install contains AL therefore it will be flagged as Area.
one way round this is to look for AL with a space either side, but from your samples & what you have said, this approach won't work as you don't always have a space before and after the search term.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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
Back
Top