Solved

Flagging cells based on dictionary in Excel 2013

Posted on 2013-06-29
12
312 Views
Last Modified: 2013-07-08
Hello, I am using Excel 2013. I have a column with several words. I want to scan the column and flag three categories:

1. Words that match a dictionary word, like plane, customer, help, etc
2. Words that contain a dictionary word, like planecs, customer11, helpmgmt, etc
3. Words that do not match any of the above

I guess if I just flag 1 and 2, the rest are 3. By flagging them I mean, if a cell is in category one, turn the background green for example. If category 2, turn the background red, something like that, as long as I can sort them. If they cannot be sorted by background color, I can always create another column next to it, and if a word is in category 1, the cell next to it should say Category 1, so I can sort that way.

Thanks!
0
Comment
Question by:it1000
  • 6
  • 2
  • 2
12 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39287127
Hi it1000,

I don't understand your question, your question it self is an answer, what the issue is?

Do you mean attached file ..?


Thanks
Conditional-Formating.xlsx
0
 

Author Comment

by:it1000
ID: 39287590
Yes I mean something like the file you attached. What are the steps to do conditional formatting based on whether a cell contains a dictionary word, or part of it contains a dictionary word?

Thanks!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39287913
Hi,

Where are the dictionary words stored that you wish to use?

BFN,

fp.
0
 

Author Comment

by:it1000
ID: 39288570
The standard American English dictionary that's built into Excel. Thanks.
0
 

Author Comment

by:it1000
ID: 39290280
Any thoughts? Thanks
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:itjockey
ID: 39290388
I don't know why you want to flag word which is not in  English dictionary - if you want to check the spelling which have red flag then there is option in excel to for spell check.

if you want to correct the spelling then there is option in excel 2010 - Top Left  corner there is one down arrow button - Click - drop down - go for More commands - one page pop up called Quick Access Toolbar - search for Spelling in left side of the window - ADD - click ok - there is new icon of spell check is available in your quick access tool bar - Click on it & check spelling.


Thanks
0
 

Author Comment

by:it1000
ID: 39290422
Thanks, but what I want to do is conditional formatting based on spellcheck, like I explained above.

Anyone? Thanks!
0
 
LVL 35

Accepted Solution

by:
[ fanpages ] earned 500 total points
ID: 39305000
fanpages:
Where are the dictionary words stored that you wish to use?
it1000:
The standard American English dictionary that's built into Excel. Thanks.

I have never seen any references /examples to gain programmatic access of explicit words within the standard dictionary word list.

If another "Expert" has, then perhaps they can advise further.  Presently though, I am unsure how to proceed with your request unless you wish to use a pre-defined list of words that may be read programmatically.  Sorry.

PS. MartinLiss provided a useful routine (using Microsoft Word to perform spell-checking on Microsoft Excel content) in one of your currently open threads:

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28177061.html#a39303117 ]

Could we utilise a similar approach here?
0
 

Author Comment

by:it1000
ID: 39306777
I actually followed the approach of having the dictionary as a column of words so that worked better, although it made excel very slow because of conditional formatting on 20,000 cells. In the end, I gave up and I am having a person do it manually.
0
 

Author Closing Comment

by:it1000
ID: 39306783
answer is in the link
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Graph of User occurance 9 27
Delete texts with font color 16 30
Access Excel export not behaving 2 25
Excel 2010 - Populate a formula in multiple cells 6 23
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now