Extract the the one or two characters of a string, depending on number of digits

[UncleBajubjubs]

Hello,
I have a cell which contains the size of an item, "Size20" for example. I have another cell which I'd like to have equal 500 if the size is 16 or larger, in which I have the formula

=IF(S22<>"",IF(VALUE(RIGHT(S22,2))>15,500,0),0)

However, this does not work for cells such as "Size8", as the last two characters are "e8".
Any suggestions on how I might resolve this?
Thanks

 

[Joe4]

Try:

[COLOR=#333333]=IF(S22<>"",IF([/COLOR]SUBSTITUTE(S22,"Size","")+0[COLOR=#333333]>15,500,0),0)[/COLOR]

 

[UncleBajubjubs]

Try:

[COLOR=#333333]=IF(S22<>"",IF([/COLOR]SUBSTITUTE(S22,"Size","")+0[COLOR=#333333]>15,500,0),0)[/COLOR]

I forgot to mention that they may have other text before the numbers instead of "Size", such as "SW8", but the numbers will always be at the end.

 

[Joe4]

I forgot to mention that they may have other text before the numbers instead of "Size", such as "SW8", but the numbers will always be at the end.

That is kind of a BIG detail to leave out, and changes things totally!

Assuming that your numbers really can only be 1 or 2 characters, try:

=IFERROR(IF(ISNUMBER(RIGHT(S22,2)+0),IF(RIGHT(S22,2)+0>15,500,0),IF(RIGHT(S22,1)+0>15,500,0)),"")

 

[DanteAmor]

How about

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80.79px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >S</td><td >T</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td >Size20</td><td style="text-align:right; ">500</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td >Size8</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td >SW8</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td >SW16</td><td style="text-align:right; ">500</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td >More text 18</td><td style="text-align:right; ">500</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >T22</td><td >=IF(S22<>"",IF(RIGHT(S22,LEN(S22)-LEN(TRIM(LEFT(S22,MIN(FIND({0,1,2,3,4,5,6,7,8,9},S22&"0123456789"))-1))))+0>15,500,0),0)</td></tr></table></td></tr></table>

 

[UncleBajubjubs]

That is kind of a BIG detail to leave out, and changes things totally!

Assuming that your numbers really can only be 1 or 2 characters, try:

=IFERROR(IF(ISNUMBER(RIGHT(S22,2)+0),IF(RIGHT(S22,2)+0>15,500,0),IF(RIGHT(S22,1)+0>15,500,0)),"")

That works, thanks!

 

[Joe4]

You are welcome.

 

[Scott Huish]

Also:
=IFERROR(RIGHT(S22,2)+0>15,0)*500

Excel 2010
	S	T
22	Size20	500
23	Size8	0
24	SW8	0
25	SW16	500
26	More text 18	500
Sheet2

Cell Formulas
Range	Formula
T22	=IFERROR(RIGHT(S22,2)+0>15,0)*500
T23	=IFERROR(RIGHT(S23,2)+0>15,0)*500
T24	=IFERROR(RIGHT(S24,2)+0>15,0)*500
T25	=IFERROR(RIGHT(S25,2)+0>15,0)*500
T26	=IFERROR(RIGHT(S26,2)+0>15,0)*500