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

UncleBajubjubs

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

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,045
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
95
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
53,045
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
11,190
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
95
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
53,045
Office Version
365
Platform
Windows
You are welcome.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,095,875
Messages
5,447,009
Members
405,428
Latest member
Cad67

This Week's Hot Topics

Top