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

UncleBajubjubs

Board Regular
Joined
Jul 11, 2017
Messages
89
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
Try:
Code:
[COLOR=#333333]=IF(S22<>"",IF([/COLOR]SUBSTITUTE(S22,"Size","")+0[COLOR=#333333]>15,500,0),0)[/COLOR]
 

UncleBajubjubs

Board Regular
Joined
Jul 11, 2017
Messages
89
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
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
Joined
Dec 3, 2018
Messages
7,682
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Jul 11, 2017
Messages
89
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
You are welcome.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
Also:
=IFERROR(RIGHT(S22,2)+0>15,0)*500

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>S</th><th>T</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Size20</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Size8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">SW8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">SW16</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">500</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">More text 18</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">500</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T22</th><td style="text-align:left">=IFERROR(<font color="Blue">RIGHT(<font color="Red">S22,2</font>)+0>15,0</font>)*500</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T23</th><td style="text-align:left">=IFERROR(<font color="Blue">RIGHT(<font color="Red">S23,2</font>)+0>15,0</font>)*500</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T24</th><td style="text-align:left">=IFERROR(<font color="Blue">RIGHT(<font color="Red">S24,2</font>)+0>15,0</font>)*500</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T25</th><td style="text-align:left">=IFERROR(<font color="Blue">RIGHT(<font color="Red">S25,2</font>)+0>15,0</font>)*500</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">T26</th><td style="text-align:left">=IFERROR(<font color="Blue">RIGHT(<font color="Red">S26,2</font>)+0>15,0</font>)*500</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Forum statistics

Threads
1,077,674
Messages
5,335,605
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top