searching for a type of string

[Roger on Excel]

I use the following to search for "M" at the end of a word :

=IF(EXACT(RIGHT(A1,1),"M"),1,0)

it returns 1 for true and 0 for false.

This is fine, but I need to expand the expression so that it returns 1 only
if the end of the string is a number followed by M.

For example :

xxxM = 0
3.2M =1
xxxm = 0
xxx m = 0
5M = 1
6.0M = 1

Can anyone help?

Thanks, Roger

 

[marcus]

Hi Roger

This should do what you wish, provided there is one character at the
end of your string as text as you had in your example. If there are
more you may need something else.

=IF(ISNUMBER(VALUE((MID(A1,1,LEN(A1)-1)))),1,0)

Take care

Marcus

 

[Ron Rosenfeld]

I use the following to search for "M" at the end of a word :

=IF(EXACT(RIGHT(A1,1),"M"),1,0)

it returns 1 for true and 0 for false.

This is fine, but I need to expand the expression so that it returns 1 only
if the end of the string is a number followed by M.

For example :

xxxM = 0
3.2M =1
xxxm = 0
xxx m = 0
5M = 1
6.0M = 1

Can anyone help?

Thanks, Roger

=SUMPRODUCT(--ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0}&"M",RIGHT(A1,2))))

--ron

 

[Rick Rothstein]

Give this a try...

=ISNUMBER(--MID(A1,LEN(A1)-1,1))*EXACT(RIGHT(A1),"M")

 

[Rick Rothstein]

Here is another way...

=ISNUMBER(FIND("x"&RIGHT(A1,2)&"x","x0Mx1Mx2Mx3Mx4Mx5Mx6Mx7Mx8Mx9Mx"))

It's longer, but it uses less function calls.