VBA code or Formula to remove reliance on Helper Column BC

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,692
Office Version
  1. 2010
Platform
  1. Windows
Dear All,

I am trying to get numbers for text containing PLS and PL. Currently
I am user helper columns which I would like to eliminate.


PLST

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 88px"><COL style="WIDTH: 64px"><COL style="WIDTH: 92px"><COL style="WIDTH: 146px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>BA</TD><TD>BB</TD><TD>BC</TD><TD>BD</TD></TR><TR style="HEIGHT: 46px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BACKGROUND-COLOR: #c0c0c0; COLOR: #0000ff; FONT-SIZE: 9pt">Level Deflating</TD><TD style="BACKGROUND-COLOR: #c0c0c0; COLOR: #0000ff; FONT-SIZE: 9pt">Level</TD><TD style="BACKGROUND-COLOR: #c0c0c0; COLOR: #0000ff; FONT-SIZE: 9pt">Helper Column</TD><TD style="BACKGROUND-COLOR: #c0c0c0; COLOR: #0000ff; FONT-SIZE: 9pt">Numbers After PL or PLS</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 9pt">4 PLST</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">4</TD><TD style="FONT-SIZE: 9pt">PLST</TD><TD style="FONT-SIZE: 9pt"></TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 9pt">5 PLS00T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">5</TD><TD style="FONT-SIZE: 9pt">PLS00T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">00</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 9pt">6 PLS012T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">6</TD><TD style="FONT-SIZE: 9pt">PLS012T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">012</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 9pt">7 PLS1000T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">7</TD><TD style="FONT-SIZE: 9pt">PLS1000T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1000</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 9pt">8 PLS1010T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">8</TD><TD style="FONT-SIZE: 9pt">PLS1010T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1010</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-SIZE: 9pt">9 PLS1030T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">9</TD><TD style="FONT-SIZE: 9pt">PLS1030T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1030</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-SIZE: 9pt">10 PLS1100T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">10</TD><TD style="FONT-SIZE: 9pt">PLS1100T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1100</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-SIZE: 9pt">11 PLS1110T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">11</TD><TD style="FONT-SIZE: 9pt">PLS1110T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1110</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-SIZE: 9pt">12 PLS1120T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">12</TD><TD style="FONT-SIZE: 9pt">PLS1120T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1120</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-SIZE: 9pt">13 PL37010</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37010</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37010</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-SIZE: 9pt">13 PL37080</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37080</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37080</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-SIZE: 9pt">13 PL37090</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37090</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37090</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-SIZE: 9pt">12 PLS1125T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">12</TD><TD style="FONT-SIZE: 9pt">PLS1125T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1125</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="FONT-SIZE: 9pt">13 PL37030</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37030</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37030</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="FONT-SIZE: 9pt">12 PLS1130T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">12</TD><TD style="FONT-SIZE: 9pt">PLS1130T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1130</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="FONT-SIZE: 9pt">13 PL37210</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37210</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37210</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="FONT-SIZE: 9pt">13 PL37230</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37230</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37230</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="FONT-SIZE: 9pt">13 PL37295</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37295</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37295</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="FONT-SIZE: 9pt">13 PLS3730T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PLS3730T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">3730</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="FONT-SIZE: 9pt">14 PL37250</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">14</TD><TD style="FONT-SIZE: 9pt">PL37250</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37250</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="FONT-SIZE: 9pt">14 PL37690</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">14</TD><TD style="FONT-SIZE: 9pt">PL37690</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37690</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD style="FONT-SIZE: 9pt">14 PL37290</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">14</TD><TD style="FONT-SIZE: 9pt">PL37290</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37290</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="FONT-SIZE: 9pt">14 PL37270</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">14</TD><TD style="FONT-SIZE: 9pt">PL37270</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37270</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="FONT-SIZE: 9pt">12 PLS1160T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">12</TD><TD style="FONT-SIZE: 9pt">PLS1160T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1160</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="FONT-SIZE: 9pt">13 PL37710</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37710</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37710</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="FONT-SIZE: 9pt">12 PLS1170T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">12</TD><TD style="FONT-SIZE: 9pt">PLS1170T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1170</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD style="FONT-SIZE: 9pt">13 PL37910</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">13</TD><TD style="FONT-SIZE: 9pt">PL37910</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37910</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</TD><TD style="FONT-SIZE: 9pt">11 PLS1190T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">11</TD><TD style="FONT-SIZE: 9pt">PLS1190T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1190</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</TD><TD style="FONT-SIZE: 9pt">12 PL37810</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">12</TD><TD style="FONT-SIZE: 9pt">PL37810</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">37810</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</TD><TD style="FONT-SIZE: 9pt"></TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD><TD style="FONT-SIZE: 9pt">#VALUE!</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</TD><TD style="FONT-SIZE: 9pt">10 PLS1011T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">10</TD><TD style="FONT-SIZE: 9pt">PLS1011T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1011</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</TD><TD style="FONT-SIZE: 9pt">11 PLS1012T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">11</TD><TD style="FONT-SIZE: 9pt">PLS1012T</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">1012</TD></TR><TR style="HEIGHT: 16px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</TD><TD style="FONT-SIZE: 9pt">12 PL35010</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">12</TD><TD style="FONT-SIZE: 9pt">PL35010</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 9pt">35010</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>BC2</TD><TD>=MID(BA2,SEARCH("PL",BA2),LEN(BA2))</TD></TR><TR><TD>BD2</TD><TD>=ExtractNumber(BC2)</TD></TR><TR><TD>BC3</TD><TD>=MID(BA3,SEARCH("PL",BA3),LEN(BA3))</TD></TR><TR><TD>BD3</TD><TD>=ExtractNumber(BC3)</TD></TR><TR><TD>BC4</TD><TD>=MID(BA4,SEARCH("PL",BA4),LEN(BA4))</TD></TR><TR><TD>BD4</TD><TD>=ExtractNumber(BC4)</TD></TR><TR><TD>BC5</TD><TD>=MID(BA5,SEARCH("PL",BA5),LEN(BA5))</TD></TR><TR><TD>BD5</TD><TD>=ExtractNumber(BC5)</TD></TR><TR><TD>BC6</TD><TD>=MID(BA6,SEARCH("PL",BA6),LEN(BA6))</TD></TR><TR><TD>BD6</TD><TD>=ExtractNumber(BC6)</TD></TR><TR><TD>BC7</TD><TD>=MID(BA7,SEARCH("PL",BA7),LEN(BA7))</TD></TR><TR><TD>BD7</TD><TD>=ExtractNumber(BC7)</TD></TR><TR><TD>BC8</TD><TD>=MID(BA8,SEARCH("PL",BA8),LEN(BA8))</TD></TR><TR><TD>BD8</TD><TD>=ExtractNumber(BC8)</TD></TR><TR><TD>BC9</TD><TD>=MID(BA9,SEARCH("PL",BA9),LEN(BA9))</TD></TR><TR><TD>BD9</TD><TD>=ExtractNumber(BC9)</TD></TR><TR><TD>BC10</TD><TD>=MID(BA10,SEARCH("PL",BA10),LEN(BA10))</TD></TR><TR><TD>BD10</TD><TD>=ExtractNumber(BC10)</TD></TR><TR><TD>BC11</TD><TD>=MID(BA11,SEARCH("PL",BA11),LEN(BA11))</TD></TR><TR><TD>BD11</TD><TD>=ExtractNumber(BC11)</TD></TR><TR><TD>BC12</TD><TD>=MID(BA12,SEARCH("PL",BA12),LEN(BA12))</TD></TR><TR><TD>BD12</TD><TD>=ExtractNumber(BC12)</TD></TR><TR><TD>BC13</TD><TD>=MID(BA13,SEARCH("PL",BA13),LEN(BA13))</TD></TR><TR><TD>BD13</TD><TD>=ExtractNumber(BC13)</TD></TR><TR><TD>BC14</TD><TD>=MID(BA14,SEARCH("PL",BA14),LEN(BA14))</TD></TR><TR><TD>BD14</TD><TD>=ExtractNumber(BC14)</TD></TR><TR><TD>BC15</TD><TD>=MID(BA15,SEARCH("PL",BA15),LEN(BA15))</TD></TR><TR><TD>BD15</TD><TD>=ExtractNumber(BC15)</TD></TR><TR><TD>BC16</TD><TD>=MID(BA16,SEARCH("PL",BA16),LEN(BA16))</TD></TR><TR><TD>BD16</TD><TD>=ExtractNumber(BC16)</TD></TR><TR><TD>BC17</TD><TD>=MID(BA17,SEARCH("PL",BA17),LEN(BA17))</TD></TR><TR><TD>BD17</TD><TD>=ExtractNumber(BC17)</TD></TR><TR><TD>BC18</TD><TD>=MID(BA18,SEARCH("PL",BA18),LEN(BA18))</TD></TR><TR><TD>BD18</TD><TD>=ExtractNumber(BC18)</TD></TR><TR><TD>BC19</TD><TD>=MID(BA19,SEARCH("PL",BA19),LEN(BA19))</TD></TR><TR><TD>BD19</TD><TD>=ExtractNumber(BC19)</TD></TR><TR><TD>BC20</TD><TD>=MID(BA20,SEARCH("PL",BA20),LEN(BA20))</TD></TR><TR><TD>BD20</TD><TD>=ExtractNumber(BC20)</TD></TR><TR><TD>BC21</TD><TD>=MID(BA21,SEARCH("PL",BA21),LEN(BA21))</TD></TR><TR><TD>BD21</TD><TD>=ExtractNumber(BC21)</TD></TR><TR><TD>BC22</TD><TD>=MID(BA22,SEARCH("PL",BA22),LEN(BA22))</TD></TR><TR><TD>BD22</TD><TD>=ExtractNumber(BC22)</TD></TR><TR><TD>BC23</TD><TD>=MID(BA23,SEARCH("PL",BA23),LEN(BA23))</TD></TR><TR><TD>BD23</TD><TD>=ExtractNumber(BC23)</TD></TR><TR><TD>BC24</TD><TD>=MID(BA24,SEARCH("PL",BA24),LEN(BA24))</TD></TR><TR><TD>BD24</TD><TD>=ExtractNumber(BC24)</TD></TR><TR><TD>BC25</TD><TD>=MID(BA25,SEARCH("PL",BA25),LEN(BA25))</TD></TR><TR><TD>BD25</TD><TD>=ExtractNumber(BC25)</TD></TR><TR><TD>BC26</TD><TD>=MID(BA26,SEARCH("PL",BA26),LEN(BA26))</TD></TR><TR><TD>BD26</TD><TD>=ExtractNumber(BC26)</TD></TR><TR><TD>BC27</TD><TD>=MID(BA27,SEARCH("PL",BA27),LEN(BA27))</TD></TR><TR><TD>BD27</TD><TD>=ExtractNumber(BC27)</TD></TR><TR><TD>BC28</TD><TD>=MID(BA28,SEARCH("PL",BA28),LEN(BA28))</TD></TR><TR><TD>BD28</TD><TD>=ExtractNumber(BC28)</TD></TR><TR><TD>BC29</TD><TD>=MID(BA29,SEARCH("PL",BA29),LEN(BA29))</TD></TR><TR><TD>BD29</TD><TD>=ExtractNumber(BC29)</TD></TR><TR><TD>BC30</TD><TD>=MID(BA30,SEARCH("PL",BA30),LEN(BA30))</TD></TR><TR><TD>BD30</TD><TD>=ExtractNumber(BC30)</TD></TR><TR><TD>BC31</TD><TD>=MID(BA31,SEARCH("PL",BA31),LEN(BA31))</TD></TR><TR><TD>BD31</TD><TD>=ExtractNumber(BC31)</TD></TR><TR><TD>BC32</TD><TD>=MID(BA32,SEARCH("PL",BA32),LEN(BA32))</TD></TR><TR><TD>BD32</TD><TD>=ExtractNumber(BC32)</TD></TR><TR><TD>BC33</TD><TD>=MID(BA33,SEARCH("PL",BA33),LEN(BA33))</TD></TR><TR><TD>BD33</TD><TD>=ExtractNumber(BC33)</TD></TR><TR><TD>BC34</TD><TD>=MID(BA34,SEARCH("PL",BA34),LEN(BA34))</TD></TR><TR><TD>BD34</TD><TD>=ExtractNumber(BC34)</TD></TR><TR><TD>BC35</TD><TD>=MID(BA35,SEARCH("PL",BA35),LEN(BA35))</TD></TR><TR><TD>BD35</TD><TD>=ExtractNumber(BC35)</TD></TR><TR><TD>BC36</TD><TD>=MID(BA36,SEARCH("PL",BA36),LEN(BA36))</TD></TR><TR><TD>BD36</TD><TD>=ExtractNumber(BC36)</TD></TR><TR><TD>BC37</TD><TD>=MID(BA37,SEARCH("PL",BA37),LEN(BA37))</TD></TR><TR><TD>BD37</TD><TD>=ExtractNumber(BC37)</TD></TR><TR><TD>BC38</TD><TD>=MID(BA38,SEARCH("PL",BA38),LEN(BA38))</TD></TR><TR><TD>BD38</TD><TD>=ExtractNumber(BC38)</TD></TR><TR><TD>BC39</TD><TD>=MID(BA39,SEARCH("PL",BA39),LEN(BA39))</TD></TR><TR><TD>BD39</TD><TD>=ExtractNumber(BC39)</TD></TR><TR><TD>BC40</TD><TD>=MID(BA40,SEARCH("PL",BA40),LEN(BA40))</TD></TR><TR><TD>BD40</TD><TD>=ExtractNumber(BC40)</TD></TR><TR><TD>BC41</TD><TD>=MID(BA41,SEARCH("PL",BA41),LEN(BA41))</TD></TR><TR><TD>BD41</TD><TD>=ExtractNumber(BC41)</TD></TR><TR><TD>BC42</TD><TD>=MID(BA42,SEARCH("PL",BA42),LEN(BA42))</TD></TR><TR><TD>BD42</TD><TD>=ExtractNumber(BC42)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Code:
Function ExtractNumber(Target As Range) As Variant
Dim i As Integer
Dim str1 As String
For i = 1 To Len(Target)
    If IsNumeric(Mid(Target, i, 1)) Then
        str1 = str1 + Mid(Target, i, 1)
    End If
Next i
ExtractNumber = str1
End Function

Your help would be greatly appreciated.

Biz
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Change the function to Target As String, and then put the formula, without the = in place of the cell reference you pass to the UDF.

So the formula would look like this:

=ExtractNumber(MID(BA2,SEARCH("PL",BA2),LEN(BA2)))

And the function like this.
Code:
Function ExtractNumber(Target As String) As Variant
Dim i As Integer
Dim str1 As String
    For i = 1 To Len(Target)
        If IsNumeric(Mid(Target, i, 1)) Then
            str1 = str1 + Mid(Target, i, 1)
        End If
    Next i
    ExtractNumber = str1
End Function
 

gregtx81

Board Regular
Joined
Feb 5, 2011
Messages
110
Unless I'm missing something, this:

Code:
BC2	=MID(BA2,SEARCH("PL",BA2),LEN(BA2))
BD2	=ExtractNumber(BC2)

can be this:


Code:
BD2	=ExtractNumber(MID(BA2,SEARCH("PL",BA2),LEN(BA2)))

edit: ...and that something would be what Norie pointed out!
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>BA</th><th>BB</th><th>BC</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C0C0C0;;">Level Deflating</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C0C0C0;;">Level</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;background-color: #C0C0C0;;">Numbers After PL or PLS</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">4 PLST</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">5 PLS00T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">00</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6 PLS012T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">012</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">7 PLS1000T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1000</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8 PLS1010T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1010</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">9 PLS1030T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1030</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">10 PLS1100T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1100</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11 PLS1110T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1110</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12 PLS1120T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1120</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37010</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37010</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37080</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37080</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37090</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37090</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12 PLS1125T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1125</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37030</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37030</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12 PLS1130T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1130</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37210</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37210</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37230</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37230</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37295</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37295</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PLS3730T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">3730</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14 PL37250</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37250</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14 PL37690</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37690</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14 PL37290</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37290</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14 PL37270</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">14</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37270</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12 PLS1160T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1160</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37710</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37710</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12 PLS1170T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1170</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13 PL37910</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">13</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37910</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11 PLS1190T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1190</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12 PL37810</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">37810</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">10 PLS1011T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">10</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1011</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11 PLS1012T</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">11</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">1012</td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12 PL35010</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">12</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">35010</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><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: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">BB2</th><td style="text-align:left">=IF(<font color="Blue">BA2="","",TRIM(<font color="Red">LEFT(<font color="Green">BA2,2</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">BC2</th><td style="text-align:left">=IF(<font color="Blue">BA2="","",TRIM(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">MID(<font color="#FF00FF">BA2,3,99</font>),"PL",""</font>),"S",""</font>),"T",""</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,692
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Thank you AlphaFrog, Norie & gregtx81 for your help.

I wanted to paste special values with code below but it drops first digits starting with zero using AlphaFrog example like BC3 and BC4.


Code:
 Range("BC1") = "Numbers After PL or PLS"
   With Range("BC2:BC" & LR)
            '.Formula = "=IF(BA2="""","""",TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(BA2,3,99),""PL"",""""),""S"",""""),""T"","""")))"  'New Method
            .Formula = "=ExtractNumber(MID(BA2,SEARCH(""PL"",BA2),LEN(BA2)))"
           .Value = .Value
   End With

Kind Regards,

Bhavik
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
Code:
    Range("BC1") = "Numbers After PL or PLS"
    v = Range("BA2:BA" & LR)
    For i = 1 To LR - 1
        v(i, 1) = Trim(Replace(Replace(Replace(Mid(v(i, 1), 3), "PL", ""), "S", ""), "T", ""))
    Next i
    Range("BC:BC").NumberFormat = "@"   'Format column as text
    Range("BC2:BC" & LR) = v
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,692
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

AlphaFrog,

I tried but it does not populate column BC.

Biz
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Bhavik

Why are you using a UDF for this?

Why not just loop and call the function in your code?

I don't think it will be any slower.

The function will be called the same no of times as it would if you used it on the worksheet.
Code:
For I = 2 To LastRow
    Range("BC"& I) = ExtractNumber(Mid(Range("BA"&I), 3))
 Next I
You can still use the UDF in the worksheet if you need it.
 

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,692
Office Version
  1. 2010
Platform
  1. Windows
What is the value of LR? I assumed you defined it previously.

Code:
Dim LR As Long
LR = Sheets("PLST").Cells(Rows.Count, "A").End(xlUp).Row

LR = 465 rows

Biz
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,448
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top