Solved

Flagging cells based on dictionary in Excel 2013

Posted on 2013-06-29
12
298 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
Comment Utility
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
Comment Utility
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 ]
Comment Utility
Hi,

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

BFN,

fp.
0
 

Author Comment

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

Author Comment

by:it1000
Comment Utility
Any thoughts? Thanks
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Expert Comment

by:itjockey
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
answer is in the link
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

743 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

16 Experts available now in Live!

Get 1:1 Help Now