searching for a type of string

  • Thread starter Thread starter Roger on Excel
  • Start date Start date
R

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
 
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
 
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
Click to expand...


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

--ron
 
Here is another way...

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

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