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,178
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,178
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,973
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,178
Office Version
365
Platform
Windows
You are welcome.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,840
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,078,516
Messages
5,340,879
Members
399,397
Latest member
VolodimiirSr

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top