 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2007getting XL to recognize lower from upper case in IF formula
Excel 2002 SP3 Win XP Pro SP2 *Follow-up to: microsoft.public.excel* Hi, I have an IF formula where =IF(LEFT(D3,3)="ABc",(B3*C3)/50,B3*C3) When LEFT(D3,3)="ABC", unfortunately, it seems XL does not see the difference between the lower case "c" in former example and upper case "C" in latter example. Is it me? Can I get around this? Use the EXACT() function  SignatureKind regards, Niek Otten Microsoft MVP - Excel | Excel 2002 SP3 | Win XP Pro SP2 [quoted text clipped - 8 lines] | Is it me? | Can I get around this? Thanks. Actually, come to think of it, if I could have a formula that does if 3 letter of D3 is lower case, then divide by 50--that would be the best. Is there a way to define "lower case" in 3rd position? > Use the EXACT() function =IF(EXACT(MID(A1,3,1),LOWER(MID(A1,3,1))),"lower","upper") SignatureJim | Thanks. | Actually, come to think of it, if I could have a formula that does if 3 | letter of D3 is lower case, then divide by 50--that would be the best. | Is there a way to define "lower case" in 3rd position? | | > Use the EXACT() function You could use something like this: =IF(CODE(MID(D3,3,1))<=90,"Upper","Lower") Where you'd replace "Upper" with what you want it to do if the 3rd character is upper case and replace "Lower" with what you want it to do if the 3rd character is lower case. This assumes that the 3rd character will always be an alpha character (no numbers or special symbols) and that your computer is using standard ASCII character set. Hope that helps. SignatureMike Lee McKinney,TX USA > Thanks. > Actually, come to think of it, if I could have a formula that does if 3 > letter of D3 is lower case, then divide by 50--that would be the best. > Is there a way to define "lower case" in 3rd position? > > > Use the EXACT() function That's good, (only letters no numbers or symbols in 3rd position). I will try it. Tx. > You could use something like this: > [quoted text clipped - 8 lines] > > Hope that helps. Perhaps I should add the D3 is the result of a formula in same cell so it may be creating this problem? Is the use of INDIRECT indicated perhaps? > Excel 2002 SP3 > Win XP Pro SP2 [quoted text clipped - 8 lines] > Is it me? > Can I get around this? >Excel 2002 SP3 >Win XP Pro SP2 [quoted text clipped - 8 lines] >Is it me? >Can I get around this? =IF(EXACT("Abc",LEFT(D3,3)),(B3*C3)/50,B3*C3) --ron Thanks >> Excel 2002 SP3 >> Win XP Pro SP2 [quoted text clipped - 12 lines] > > --ron
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.