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

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

On Mon, 4 Jan 2010 17:40:02 -0800, Roger on Excel
wrote:

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

Give this a try...

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

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
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

Here is another way...

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

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

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Give this a try...

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

--
Rick (MVP - Excel)


"Roger on Excel" wrote in message
...
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