Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lower-Case Characters
I am using Excel 2007 on a Windows 7 PC.
I have a long Excel list of part numbers with their description. The company rule states that the description for each part number must be all in upper case alphabetical characters, but some are not. How would I quickly flag which parts have descriptions that do not fit the rule? To clarify: If the description is _all_ in upper case alphabetical characters (with the exception of digits or special characters, of course!) then the rule is met. If the description is in lower case characters (even if it is only one lower case alphabet character within the string) then the rule is not met. Thanks. -- tb |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lower-Case Characters
"tb" wrote:
I have a long Excel list of part numbers with their description. The company rule states that the description for each part number must be all in upper case alphabetical characters, but some are not. How would I quickly flag which parts have descriptions that do not fit the rule? Suppose the part numbers are in column A starting with in row 2. In a parallel column, array-enter the following formula (press ctrl+shift+Enter instead of just Enter): =IF(SUM(IF(ISERROR(FIND({"a","b","c","d","e","f"," g","h","i", "j","k","l","m","n","o","p","q","r","s","t","u","v ","w","x", "y","z"},A2)),0,1))=0,"","ERROR") Copy the formula down the parallel column. To find all the "ERROR" rows, highlight the parallel column, press ctrl+F (Find), enter "ERROR" without quotes in Find What, click on Options and select Look In Values, then click on Find All. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lower-Case Characters
On 7/3/2013 at 1:47:45 AM joeu2004 wrote:
=IF(SUM(IF(ISERROR(FIND({"a","b","c","d","e","f"," g","h","i", "j","k","l","m","n","o","p","q","r","s","t","u","v ","w","x", "y","z"},A2)),0,1))=0,"","ERROR") Thank you, joeu2004, it works perfectly for what I initially asked. Unfortunately, I researched the situation a little further and it is more complicated than I initially thought... A few of the descriptions are not in the Roman alphabet. They appear to be in the Cyrillic alphabet or something similar to that! We have a common ERP system used by branches in different countries. We think that somebody from our Ukraine or Russian branches accidentally overrode some of the English descriptions of some parts. (Our ERP system allows to enter the description of any part number in several languages. What they did is they accidentally entered the description in their own language in the field reserved for the English version... Or something like that!) Yes, I could ask them to rectify the mistake but -- for reasons I'd rather not discuss here -- we would prefer making the corrections ourselves. The problem is that we have thousands of part numbers and I need a quick way to segregate those that have descriptions that: a) Were made using lower-case alphabet characters. (And 10eu2004 solved that!), but also b) Were entered using non-Roman alphabet characters. It does not matter if lower or upper case. What a mess... If somebody comes up with a formula that will help me solve this problem I will be eternally thankful to him/her! Thanks. -- tb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minor puzzle: some UDF calls respect mixed case, others insist on lower case | Excel Programming | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Help w/ project. Counting upper and lower case characters in a cell. | Excel Programming | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) |