I need to search for a word with a capital letter at the end of the string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger

hi
excel usually doesn't distinguish between upper and lower case so you have
to be specific about the character.
=if(right(D18,1) = CHAR(77), "Yes","no")

in ASCII, the uppercase M is character 77, the lower case m is character 109.
for future reference....
=char(77) and =char(109)
to see all...
in A1 enter =char(row())
copy down to row 256.
some of the characters are "non-printable" characters like space, tab,
backspace so you will only se a small square. some are hold overs from old
teletype days(the origin of ASCII) like begin text, end text, end
transmittion, devise control 2(whatever that is).

regards
FSt1

"Roger on Excel" wrote:

I need to search for a word with a capital letter at the end of the string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger

hi
if you what to "see" the non-printable characters...
http://www.asciitable.com/

regards
FSt1

"FSt1" wrote:

hi
excel usually doesn't distinguish between upper and lower case so you have
to be specific about the character.
=if(right(D18,1) = CHAR(77), "Yes","no")

in ASCII, the uppercase M is character 77, the lower case m is character 109.
for future reference....
=char(77) and =char(109)
to see all...
in A1 enter =char(row())
copy down to row 256.
some of the characters are "non-printable" characters like space, tab,
backspace so you will only se a small square. some are hold overs from old
teletype days(the origin of ASCII) like begin text, end text, end
transmittion, devise control 2(whatever that is).

regards
FSt1

"Roger on Excel" wrote:

I need to search for a word with a capital letter at the end of the string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger

Thanks for the help,

Best regards,

Roger

"FSt1" wrote:

hi
if you what to "see" the non-printable characters...
http://www.asciitable.com/

regards
FSt1

"FSt1" wrote:

hi
excel usually doesn't distinguish between upper and lower case so you have
to be specific about the character.
=if(right(D18,1) = CHAR(77), "Yes","no")

in ASCII, the uppercase M is character 77, the lower case m is character 109.
for future reference....
=char(77) and =char(109)
to see all...
in A1 enter =char(row())
copy down to row 256.
some of the characters are "non-printable" characters like space, tab,
backspace so you will only se a small square. some are hold overs from old
teletype days(the origin of ASCII) like begin text, end text, end
transmittion, devise control 2(whatever that is).

regards
FSt1

"Roger on Excel" wrote:

I need to search for a word with a capital letter at the end of the string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger

Try this:

=IF(EXACT(RIGHT(D18),"M"),"yes","no")

--
Biff
Microsoft Excel MVP


"Roger on Excel" wrote in message
...
I need to search for a word with a capital letter at the end of the string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt
differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger

You can use the "FIND" function which differentiates between upper and
lowercase (similar to SEARCH" function)
On Jul 22, 7:26*am, Roger on Excel
wrote:
I need to search for a word with a capital letter at the end of the string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger

=IF(ISERROR(FIND("R",RIGHT(E6,1),1)),"no","yes")

"Manic" wrote:

You can use the "FIND" function which differentiates between upper and
lowercase (similar to SEARCH" function)
On Jul 22, 7:26 am, Roger on Excel
wrote:
I need to search for a word with a capital letter at the end of the string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger

=IF(ISERROR(FIND("R",RIGHT(E6,1),1)),"no","yes")

A few keystrokes shorter:

=IF(COUNT(FIND("R",RIGHT(E6))),"yes","no")


--
Biff
Microsoft Excel MVP


"Excel_Learner" wrote in message
...
=IF(ISERROR(FIND("R",RIGHT(E6,1),1)),"no","yes")

"Manic" wrote:

You can use the "FIND" function which differentiates between upper and
lowercase (similar to SEARCH" function)
On Jul 22, 7:26 am, Roger on Excel
wrote:
I need to search for a word with a capital letter at the end of the
string.

For example

Hydrochloric acid 1M

Ive tried using =IF(RIGHT(D18)="M","yes","no"), but it doesnt
differentiate
between lower case and upper case.

Can anyone help?

Thanks,

Roger