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

getting XL to recognize lower from upper case in IF formula
 

Use the EXACT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"stef" wrote in message ...
| 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?

getting XL to recognize lower from upper case in IF formula
 

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?

getting XL to recognize lower from upper case in IF formula
 

On Thu, 06 Dec 2007 16:35:32 -0500, 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?


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


--ron

getting XL to recognize lower from upper case in IF formula
 

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

getting XL to recognize lower from upper case in IF formula
 

Thanks

Ron Rosenfeld wrote:
On Thu, 06 Dec 2007 16:35:32 -0500, 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?


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


--ron

getting XL to recognize lower from upper case in IF formula
 

=IF(EXACT(MID(A1,3,1),LOWER(MID(A1,3,1))),"lower", "upper")

--
Jim
"stef" wrote in message
...
| 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
|

getting XL to recognize lower from upper case in IF formula
 

Thanks

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

getting XL to recognize lower from upper case in IF formula
 

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

getting XL to recognize lower from upper case in IF formula
 

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.