Solved

Flagging cells based on dictionary in Excel 2013

Posted on 2013-06-29
12
321 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

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 article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

805 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