searching for a type of string
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 |
searching for a type of string
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 |
searching for a type of string
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 |
searching for a type of string
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 |
searching for a type of string
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 |
All times are GMT +1. The time now is 08:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com