Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Flagging cells based on dictionary in Excel 2013

Posted on 2013-06-29
12
Medium Priority
?
354 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:Naresh Patel
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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:Naresh Patel
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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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‚Ķ

916 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