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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
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
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
184
Office Version
  1. 365
Platform
  1. Windows
tried copying code and typing code both ways gives me an error and highlights the "AL" part of the code.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Try changing the semi-colons to commas
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
184
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Will the AL always be in capitals & could you have other words that contain AL that should be ignored?
 

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
184
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

Fluff

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

fvisions

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

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,127,665
Messages
5,626,173
Members
416,166
Latest member
Archimed

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