search for words with capital letter at end
 

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

search for words with capital letter at end
 

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

search for words with capital letter at end
 

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

search for words with capital letter at end
 

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

search for words with capital letter at end
 

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

search for words with capital letter at end
 

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

search for words with capital letter at end
 

=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

search for words with capital letter at end
 

=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