Forum

Notifications
Clear all

Search and highlight errors in naming convention

3 Posts
2 Users
0 Reactions
54 Views
(@nffcspw)
Posts: 2
New Member
Topic starter
 

I have a column with 10000+ rows containing text.  The text follows a naming convention of TEXT - TEXT - TEXT.  I want to check for all rows where there are no spaces before and after each instance of "-" . For example "Text- Text - Text", "Text -Text - Text" and "Text - Text- Text" etc should all be highlighted as errors. Each text string includes letters and numbers and can have between 1 and 5 instances of "-" in each.  I have tried various options in the regular find/replace tool including wildcards without success.  I'm guessing I'll have to go down the VBA route but I can't figure out how. Can anyone advise please?

 
Posted : 23/12/2016 10:58 am
(@sunnykow)
Posts: 1417
Noble Member
 

Hi

Give this a try

=IF(ISERROR(FIND("-",SUBSTITUTE(A1," - ",""))),"OK","ERROR")

Sunny

 
Posted : 23/12/2016 11:23 am
(@nffcspw)
Posts: 2
New Member
Topic starter
 

Brilliant, thank you Sunny

 
Posted : 24/12/2016 7:35 am
Share: