VBA code or Formula to remove reliance on Helper Column BC

[Biz]

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>

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

 

[Biz]

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.

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.

Hi Norie,

I have used your suggestion and it works fine thank you.

Biz

 

[Biz]

Hi,

I managed to convert number to text by using code below

Range("BC1") = "Numbers After PL or PLS"
  For i = 2 To LR
    Range("BC" & i) = "'" & ExtractNumber(Mid(Range("BA" & i), 3)) 'add a single quote to the front of the value
  Next i

Thanks for your help.

Biz