Incorrect Formula Error

[jermsalerms]

Cell 'Import Sheet'!X2 contains text in both uppercase, propercasing, and lowercasing....such as YES, NO, Yes, No, yes, no, Unknown

I am trying to write a formula that will convert everything to uppercase...trim to just the 1st character...then determine if Y then result is Y...if N then result is N...if anything else it is blank

here is what I wrote and I get a formula error:

=IF((PROPER(LEFT('Import Sheet'!X2,1)))="Y","Y",(PROPER(LEFT('Import Sheet'!X2,1)))="N","N","")

[Bernard Liengme]

You forgot the second IF
=IF(PROPER(LEFT('Import Sheet'!X2,1))="Y","Y",IF(PROPER(LEFT('Import
Sheet'!X2,1))="N","N","?"))
But why bother, Excel is not case sensitive, so this gives same result
=IF(LEFT('Import Sheet'!X2,1)="Y","Y",IF(LEFT('Import
Sheet'!X2,1)="N","N","?"))
best wishes
--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"jermsalerms" <[email protected]>
wrote in message
news:[email protected]...
>
> Cell 'Import Sheet'!X2 contains text in both uppercase, propercasing,
> and lowercasing....such as YES, NO, Yes, No, yes, no, Unknown
>
> I am trying to write a formula that will convert everything to
> uppercase...trim to just the 1st character...then determine if Y then
> result is Y...if N then result is N...if anything else it is blank
>
> here is what I wrote and I get a formula error:
>
> =IF((PROPER(LEFT('Import Sheet'!X2,1)))="Y","Y",(PROPER(LEFT('Import
> Sheet'!X2,1)))="N","N","")
>
>
> --
> jermsalerms
> ------------------------------------------------------------------------
> jermsalerms's Profile:
> http://www.excelforum.com/member.php...o&userid=30167
> View this thread: http://www.excelforum.com/showthread...hreadid=502546
>

[jermsalerms]

The formula you suggested is working if the first letter is Y....but if the first letter is N or something else I get #REF