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

#### UncleBajubjubs

##### Board Regular
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:
Code:
``[COLOR=#333333]=IF(S22<>"",IF([/COLOR]SUBSTITUTE(S22,"Size","")+0[COLOR=#333333]>15,500,0),0)[/COLOR]``

#### UncleBajubjubs

##### Board Regular
Try:
Code:
``[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:
Code:
``=IFERROR(IF(ISNUMBER(RIGHT(S22,2)+0),IF(RIGHT(S22,2)+0>15,500,0),IF(RIGHT(S22,1)+0>15,500,0)),"")``

#### DanteAmor

##### Well-known Member

<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

##### Board Regular
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:
Code:
``=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!

Last edited:

You are welcome.

#### Scott Huish

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

Last edited: