Microsoft Office Forums


Reply
Thread Tools Display Modes

getting XL to recognize lower from upper case in IF formula

 
 
stef
Guest
Posts: n/a
 
      12-06-2007, 08:35 PM
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?
 
Reply With Quote
 
 
 
 
Niek Otten
Guest
Posts: n/a
 
      12-06-2007, 08:40 PM
Use the EXACT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"stef" <(E-Mail Removed)> wrote in message news:%23TzKv$(E-Mail Removed)...
| 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?


 
Reply With Quote
 
stef
Guest
Posts: n/a
 
      12-06-2007, 08:42 PM
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?

stef wrote:
> 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?

 
Reply With Quote
 
Ron Rosenfeld
Guest
Posts: n/a
 
      12-06-2007, 08:46 PM
On Thu, 06 Dec 2007 16:35:32 -0500, stef <(E-Mail Removed)> wrote:

>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?



=IF(EXACT("Abc",LEFT(D3,3)),(B3*C3)/50,B3*C3)


--ron
 
Reply With Quote
 
stef
Guest
Posts: n/a
 
      12-06-2007, 08:54 PM
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?

Niek Otten wrote:
> Use the EXACT() function
>

 
Reply With Quote
 
stef
Guest
Posts: n/a
 
      12-06-2007, 09:06 PM
Thanks

Ron Rosenfeld wrote:
> On Thu, 06 Dec 2007 16:35:32 -0500, stef <(E-Mail Removed)> wrote:
>
>> 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?

>
>
> =IF(EXACT("Abc",LEFT(D3,3)),(B3*C3)/50,B3*C3)
>
>
> --ron

 
Reply With Quote
 
Jim Rech
Guest
Posts: n/a
 
      12-06-2007, 09:08 PM
=IF(EXACT(MID(A1,3,1),LOWER(MID(A1,3,1))),"lower", "upper")

--
Jim
"stef" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
| 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?
|
| Niek Otten wrote:
| > Use the EXACT() function
| >


 
Reply With Quote
 
stef
Guest
Posts: n/a
 
      12-06-2007, 09:13 PM
Thanks

Jim Rech wrote:
> =IF(EXACT(MID(A1,3,1),LOWER(MID(A1,3,1))),"lower", "upper")
>

 
Reply With Quote
 
mikelee101
Guest
Posts: n/a
 
      12-06-2007, 09:17 PM
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.
--
Mike Lee
McKinney,TX USA


"stef" wrote:

> 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?
>
> Niek Otten wrote:
> > Use the EXACT() function
> >

>

 
Reply With Quote
 
stef
Guest
Posts: n/a
 
      12-06-2007, 10:42 PM
That's good, (only letters no numbers or symbols in 3rd position). I
will try it. Tx.

mikelee101 wrote:
> 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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Upper case letter change to lower case when opened on another comp Kleesg PowerPoint Newsgroup 1 10-24-2008 09:17 PM
Change all upper case letters to lower case Need2know Access Newsgroup 1 03-16-2007 08:04 PM
Is it possible to change from lower case to upper case text S.Purushothaman Project Newsgroup 2 05-27-2006 04:22 PM
Convert UPPER CASE to Lower Case? Newbie Excel Newsgroup 6 07-21-2005 09:08 AM
Converting from upper case to lower case Simple Excel Newsgroup 4 07-08-2004 03:29 PM



All times are GMT. The time now is 04:29 PM.
Microsoft Office Forums is not affiliated with Microsoft Corporation.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92