Lower-Case Characters

  • Thread starter Thread starter tb
  • Start date Start date
T

tb

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 said:
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?
Click to expand...

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.
 
=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")
Click to expand...

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.
 
Back
Top