Solved

Flagging cells based on dictionary in Excel 2013

Posted on 2013-06-29
12
334 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA modules import 4 57
Do Wend Macro not working 22 36
HOW I CAN MANUALLY Format AN COLUMN ! 2 18
Help with Excel 18 33
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

821 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