VBA code or Formula to remove reliance on Helper Column BC

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
 
Upvote 0
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!
 
Upvote 0
<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 />
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
AlphaFrog,

I tried but it does not populate column BC.

Biz
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top