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 |
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. |
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 |
All times are GMT +1. The time now is 04:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com