Hello
I have some data in column AV.
new
<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: 233px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 57px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AV</TD><TD>AW</TD><TD>AX</TD><TD>AY</TD><TD>AZ</TD><TD>BA</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="COLOR: #008000">13.80 22.89 25.12 25.28 23.80</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">13.80</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">22.89</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">25.12</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">25.28</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">23.80</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>14.16 22.03 23.69 24.23 23.62</TD><TD style="TEXT-ALIGN: right">14.16 </TD><TD style="TEXT-ALIGN: right">22.03 </TD><TD style="TEXT-ALIGN: right">23.69 </TD><TD style="TEXT-ALIGN: right">24.23 </TD><TD style="TEXT-ALIGN: right">23.62</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>13.99 22.42 26.44 26.20 24.78 23.99</TD><TD style="TEXT-ALIGN: right">13.99 </TD><TD style="TEXT-ALIGN: right">22.42 </TD><TD style="TEXT-ALIGN: right">26.44 </TD><TD style="TEXT-ALIGN: right">26.20 </TD><TD style="TEXT-ALIGN: right">24.78</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>13.7 22.9 24.4 26.1 23.5</TD><TD style="TEXT-ALIGN: right">13.7 </TD><TD>2.9 2</TD><TD>.4 26</TD><TD>1 23.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>13.7 22.9 24.5 25.0 23.9</TD><TD style="TEXT-ALIGN: right">13.7 </TD><TD>2.9 2</TD><TD>.5 25</TD><TD>0 23.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>26.0 24.6 24.6 24.0 24.0</TD><TD style="TEXT-ALIGN: right">26.0 </TD><TD>4.6 2</TD><TD>.6 24</TD><TD>0 24.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>14.2 22.6 24.0 23.9 23.2</TD><TD style="TEXT-ALIGN: right">14.2 </TD><TD>2.6 2</TD><TD>.0 23</TD><TD>9 23.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>24.6 23.2 23.8 23.7</TD><TD style="TEXT-ALIGN: right">24.6 </TD><TD>3.2 2</TD><TD>.8 23</TD><TD style="TEXT-ALIGN: right">7</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>24.5 22.3 24.6 23.2</TD><TD style="TEXT-ALIGN: right">24.5 </TD><TD>2.3 2</TD><TD>.6 23</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; 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>AW7</TD><TD>=MID(AV7,1,5)</TD></TR><TR><TD>AX7</TD><TD>=MID(AV7,8,5)</TD></TR><TR><TD>AY7</TD><TD>=MID(AV7,15,5)</TD></TR><TR><TD>AZ7</TD><TD>=MID(AV7,22,5)</TD></TR><TR><TD>BA7</TD><TD>=MID(AV7,29,5)</TD></TR><TR><TD>AW8</TD><TD>=MID(AV8,1,6)</TD></TR><TR><TD>AX8</TD><TD>=MID(AV8,8,6)</TD></TR><TR><TD>AY8</TD><TD>=MID(AV8,15,6)</TD></TR><TR><TD>AZ8</TD><TD>=MID(AV8,22,6)</TD></TR><TR><TD>BA8</TD><TD>=MID(AV8,29,5)</TD></TR><TR><TD>AW9</TD><TD>=MID(AV9,1,6)</TD></TR><TR><TD>AX9</TD><TD>=MID(AV9,8,6)</TD></TR><TR><TD>AY9</TD><TD>=MID(AV9,15,6)</TD></TR><TR><TD>AZ9</TD><TD>=MID(AV9,22,6)</TD></TR><TR><TD>BA9</TD><TD>=MID(AV9,29,5)</TD></TR><TR><TD>AW10</TD><TD>=MID(AV10,1,6)</TD></TR><TR><TD>AX10</TD><TD>=MID(AV10,8,6)</TD></TR><TR><TD>AY10</TD><TD>=MID(AV10,15,6)</TD></TR><TR><TD>AZ10</TD><TD>=MID(AV10,22,6)</TD></TR><TR><TD>BA10</TD><TD>=MID(AV10,29,5)</TD></TR><TR><TD>AW11</TD><TD>=MID(AV11,1,6)</TD></TR><TR><TD>AX11</TD><TD>=MID(AV11,8,6)</TD></TR><TR><TD>AY11</TD><TD>=MID(AV11,15,6)</TD></TR><TR><TD>AZ11</TD><TD>=MID(AV11,22,6)</TD></TR><TR><TD>BA11</TD><TD>=MID(AV11,29,5)</TD></TR><TR><TD>AW12</TD><TD>=MID(AV12,1,6)</TD></TR><TR><TD>AX12</TD><TD>=MID(AV12,8,6)</TD></TR><TR><TD>AY12</TD><TD>=MID(AV12,15,6)</TD></TR><TR><TD>AZ12</TD><TD>=MID(AV12,22,6)</TD></TR><TR><TD>BA12</TD><TD>=MID(AV12,29,5)</TD></TR><TR><TD>AW13</TD><TD>=MID(AV13,1,6)</TD></TR><TR><TD>AX13</TD><TD>=MID(AV13,8,6)</TD></TR><TR><TD>AY13</TD><TD>=MID(AV13,15,6)</TD></TR><TR><TD>AZ13</TD><TD>=MID(AV13,22,6)</TD></TR><TR><TD>BA13</TD><TD>=MID(AV13,29,5)</TD></TR><TR><TD>AW14</TD><TD>=MID(AV14,1,6)</TD></TR><TR><TD>AX14</TD><TD>=MID(AV14,8,6)</TD></TR><TR><TD>AY14</TD><TD>=MID(AV14,15,6)</TD></TR><TR><TD>AZ14</TD><TD>=MID(AV14,22,6)</TD></TR><TR><TD>BA14</TD><TD>=MID(AV14,29,5)</TD></TR><TR><TD>AW15</TD><TD>=MID(AV15,1,6)</TD></TR><TR><TD>AX15</TD><TD>=MID(AV15,8,6)</TD></TR><TR><TD>AY15</TD><TD>=MID(AV15,15,6)</TD></TR><TR><TD>AZ15</TD><TD>=MID(AV15,22,6)</TD></TR><TR><TD>BA15</TD><TD>=MID(AV15,29,5)</TD></TR><TR><TD>AW16</TD><TD>=MID(AV16,1,6)</TD></TR><TR><TD>AX16</TD><TD>=MID(AV16,8,6)</TD></TR><TR><TD>AY16</TD><TD>=MID(AV16,15,6)</TD></TR><TR><TD>AZ16</TD><TD>=MID(AV16,22,6)</TD></TR><TR><TD>BA16</TD><TD>=MID(AV16,29,5)</TD></TR><TR><TD>AW17</TD><TD>=MID(AV17,1,6)</TD></TR><TR><TD>AX17</TD><TD>=MID(AV17,8,6)</TD></TR><TR><TD>AY17</TD><TD>=MID(AV17,15,6)</TD></TR><TR><TD>AZ17</TD><TD>=MID(AV17,22,6)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I had a simple formula that worked to extract numbers (5 characters) from the string. The values were placed in columns AW:BA.
If there were only 4 values to be extracted then AW:AZ were the destination cells.
This formula does not work, however, with older data (AV10:AV15).
Can someone please work out a formula that can be copied and pasted that:
1) extracts data so that the following is the desired result
Sheet5
<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: 228px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AV</TD><TD>AW</TD><TD>AX</TD><TD>AY</TD><TD>AZ</TD><TD>BA</TD><TD>BB</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>13.80 22.89 25.12 25.28 23.80</TD><TD style="TEXT-ALIGN: right">13.80</TD><TD style="TEXT-ALIGN: right">22.89</TD><TD style="TEXT-ALIGN: right">25.12</TD><TD style="TEXT-ALIGN: right">25.28</TD><TD style="TEXT-ALIGN: right">23.80</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>14.16 22.03 23.69 24.23 23.62</TD><TD style="TEXT-ALIGN: right">14.16</TD><TD style="TEXT-ALIGN: right">22.03</TD><TD style="TEXT-ALIGN: right">23.69</TD><TD style="TEXT-ALIGN: right">24.23</TD><TD style="TEXT-ALIGN: right">23.62</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>13.99 22.42 26.44 26.20 24.78 23.99</TD><TD style="TEXT-ALIGN: right">13.99</TD><TD style="TEXT-ALIGN: right">22.42</TD><TD style="TEXT-ALIGN: right">26.40</TD><TD style="TEXT-ALIGN: right">26.20</TD><TD style="TEXT-ALIGN: right">24.78</TD><TD style="TEXT-ALIGN: right">23.99</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>13.7 22.9 24.4 26.1 23.5</TD><TD style="TEXT-ALIGN: right">13.70</TD><TD style="TEXT-ALIGN: right">22.90</TD><TD style="TEXT-ALIGN: right">24.40</TD><TD style="TEXT-ALIGN: right">26.10</TD><TD style="TEXT-ALIGN: right">23.50</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>13.7 22.9 24.5 25.0 23.9</TD><TD style="TEXT-ALIGN: right">13.70</TD><TD style="TEXT-ALIGN: right">22.90</TD><TD style="TEXT-ALIGN: right">24.50</TD><TD style="TEXT-ALIGN: right">25.00</TD><TD style="TEXT-ALIGN: right">23.90</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>26.0 24.6 24.6 24.0 24.0</TD><TD style="TEXT-ALIGN: right">26.00</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">24.00</TD><TD style="TEXT-ALIGN: right">24.00</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>14.2 22.6 24.0 23.9 23.2</TD><TD style="TEXT-ALIGN: right">14.20</TD><TD style="TEXT-ALIGN: right">22.60</TD><TD style="TEXT-ALIGN: right">24.00</TD><TD style="TEXT-ALIGN: right">23.90</TD><TD style="TEXT-ALIGN: right">23.20</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>24.6 23.2 23.8 23.7</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">23.20</TD><TD style="TEXT-ALIGN: right">23.80</TD><TD style="TEXT-ALIGN: right">23.70</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>24.5 22.3 24.6 23.2</TD><TD style="TEXT-ALIGN: right">24.50</TD><TD style="TEXT-ALIGN: right">22.30</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">23.20</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
2) Extract 4,5 or 6 values depending on the original string in the cell.
3) make the cells with no data blank
The formula I would be looking for would need to be copied and pasted down several thousands of rows, of which are a mixture of 4,5 and 6 values that need to be extracted.
Thanking you in advance for your help.
I have some data in column AV.
new
<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: 233px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 57px"><COL style="WIDTH: 70px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AV</TD><TD>AW</TD><TD>AX</TD><TD>AY</TD><TD>AZ</TD><TD>BA</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="COLOR: #008000">13.80 22.89 25.12 25.28 23.80</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">13.80</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">22.89</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">25.12</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">25.28</TD><TD style="TEXT-ALIGN: right; COLOR: #008000">23.80</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>14.16 22.03 23.69 24.23 23.62</TD><TD style="TEXT-ALIGN: right">14.16 </TD><TD style="TEXT-ALIGN: right">22.03 </TD><TD style="TEXT-ALIGN: right">23.69 </TD><TD style="TEXT-ALIGN: right">24.23 </TD><TD style="TEXT-ALIGN: right">23.62</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>13.99 22.42 26.44 26.20 24.78 23.99</TD><TD style="TEXT-ALIGN: right">13.99 </TD><TD style="TEXT-ALIGN: right">22.42 </TD><TD style="TEXT-ALIGN: right">26.44 </TD><TD style="TEXT-ALIGN: right">26.20 </TD><TD style="TEXT-ALIGN: right">24.78</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>13.7 22.9 24.4 26.1 23.5</TD><TD style="TEXT-ALIGN: right">13.7 </TD><TD>2.9 2</TD><TD>.4 26</TD><TD>1 23.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>13.7 22.9 24.5 25.0 23.9</TD><TD style="TEXT-ALIGN: right">13.7 </TD><TD>2.9 2</TD><TD>.5 25</TD><TD>0 23.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>26.0 24.6 24.6 24.0 24.0</TD><TD style="TEXT-ALIGN: right">26.0 </TD><TD>4.6 2</TD><TD>.6 24</TD><TD>0 24.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>14.2 22.6 24.0 23.9 23.2</TD><TD style="TEXT-ALIGN: right">14.2 </TD><TD>2.6 2</TD><TD>.0 23</TD><TD>9 23.</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>24.6 23.2 23.8 23.7</TD><TD style="TEXT-ALIGN: right">24.6 </TD><TD>3.2 2</TD><TD>.8 23</TD><TD style="TEXT-ALIGN: right">7</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>24.5 22.3 24.6 23.2</TD><TD style="TEXT-ALIGN: right">24.5 </TD><TD>2.3 2</TD><TD>.6 23</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; 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>AW7</TD><TD>=MID(AV7,1,5)</TD></TR><TR><TD>AX7</TD><TD>=MID(AV7,8,5)</TD></TR><TR><TD>AY7</TD><TD>=MID(AV7,15,5)</TD></TR><TR><TD>AZ7</TD><TD>=MID(AV7,22,5)</TD></TR><TR><TD>BA7</TD><TD>=MID(AV7,29,5)</TD></TR><TR><TD>AW8</TD><TD>=MID(AV8,1,6)</TD></TR><TR><TD>AX8</TD><TD>=MID(AV8,8,6)</TD></TR><TR><TD>AY8</TD><TD>=MID(AV8,15,6)</TD></TR><TR><TD>AZ8</TD><TD>=MID(AV8,22,6)</TD></TR><TR><TD>BA8</TD><TD>=MID(AV8,29,5)</TD></TR><TR><TD>AW9</TD><TD>=MID(AV9,1,6)</TD></TR><TR><TD>AX9</TD><TD>=MID(AV9,8,6)</TD></TR><TR><TD>AY9</TD><TD>=MID(AV9,15,6)</TD></TR><TR><TD>AZ9</TD><TD>=MID(AV9,22,6)</TD></TR><TR><TD>BA9</TD><TD>=MID(AV9,29,5)</TD></TR><TR><TD>AW10</TD><TD>=MID(AV10,1,6)</TD></TR><TR><TD>AX10</TD><TD>=MID(AV10,8,6)</TD></TR><TR><TD>AY10</TD><TD>=MID(AV10,15,6)</TD></TR><TR><TD>AZ10</TD><TD>=MID(AV10,22,6)</TD></TR><TR><TD>BA10</TD><TD>=MID(AV10,29,5)</TD></TR><TR><TD>AW11</TD><TD>=MID(AV11,1,6)</TD></TR><TR><TD>AX11</TD><TD>=MID(AV11,8,6)</TD></TR><TR><TD>AY11</TD><TD>=MID(AV11,15,6)</TD></TR><TR><TD>AZ11</TD><TD>=MID(AV11,22,6)</TD></TR><TR><TD>BA11</TD><TD>=MID(AV11,29,5)</TD></TR><TR><TD>AW12</TD><TD>=MID(AV12,1,6)</TD></TR><TR><TD>AX12</TD><TD>=MID(AV12,8,6)</TD></TR><TR><TD>AY12</TD><TD>=MID(AV12,15,6)</TD></TR><TR><TD>AZ12</TD><TD>=MID(AV12,22,6)</TD></TR><TR><TD>BA12</TD><TD>=MID(AV12,29,5)</TD></TR><TR><TD>AW13</TD><TD>=MID(AV13,1,6)</TD></TR><TR><TD>AX13</TD><TD>=MID(AV13,8,6)</TD></TR><TR><TD>AY13</TD><TD>=MID(AV13,15,6)</TD></TR><TR><TD>AZ13</TD><TD>=MID(AV13,22,6)</TD></TR><TR><TD>BA13</TD><TD>=MID(AV13,29,5)</TD></TR><TR><TD>AW14</TD><TD>=MID(AV14,1,6)</TD></TR><TR><TD>AX14</TD><TD>=MID(AV14,8,6)</TD></TR><TR><TD>AY14</TD><TD>=MID(AV14,15,6)</TD></TR><TR><TD>AZ14</TD><TD>=MID(AV14,22,6)</TD></TR><TR><TD>BA14</TD><TD>=MID(AV14,29,5)</TD></TR><TR><TD>AW15</TD><TD>=MID(AV15,1,6)</TD></TR><TR><TD>AX15</TD><TD>=MID(AV15,8,6)</TD></TR><TR><TD>AY15</TD><TD>=MID(AV15,15,6)</TD></TR><TR><TD>AZ15</TD><TD>=MID(AV15,22,6)</TD></TR><TR><TD>BA15</TD><TD>=MID(AV15,29,5)</TD></TR><TR><TD>AW16</TD><TD>=MID(AV16,1,6)</TD></TR><TR><TD>AX16</TD><TD>=MID(AV16,8,6)</TD></TR><TR><TD>AY16</TD><TD>=MID(AV16,15,6)</TD></TR><TR><TD>AZ16</TD><TD>=MID(AV16,22,6)</TD></TR><TR><TD>BA16</TD><TD>=MID(AV16,29,5)</TD></TR><TR><TD>AW17</TD><TD>=MID(AV17,1,6)</TD></TR><TR><TD>AX17</TD><TD>=MID(AV17,8,6)</TD></TR><TR><TD>AY17</TD><TD>=MID(AV17,15,6)</TD></TR><TR><TD>AZ17</TD><TD>=MID(AV17,22,6)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I had a simple formula that worked to extract numbers (5 characters) from the string. The values were placed in columns AW:BA.
If there were only 4 values to be extracted then AW:AZ were the destination cells.
This formula does not work, however, with older data (AV10:AV15).
Can someone please work out a formula that can be copied and pasted that:
1) extracts data so that the following is the desired result
Sheet5
<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: 228px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"><COL style="WIDTH: 62px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>AV</TD><TD>AW</TD><TD>AX</TD><TD>AY</TD><TD>AZ</TD><TD>BA</TD><TD>BB</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>13.80 22.89 25.12 25.28 23.80</TD><TD style="TEXT-ALIGN: right">13.80</TD><TD style="TEXT-ALIGN: right">22.89</TD><TD style="TEXT-ALIGN: right">25.12</TD><TD style="TEXT-ALIGN: right">25.28</TD><TD style="TEXT-ALIGN: right">23.80</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>14.16 22.03 23.69 24.23 23.62</TD><TD style="TEXT-ALIGN: right">14.16</TD><TD style="TEXT-ALIGN: right">22.03</TD><TD style="TEXT-ALIGN: right">23.69</TD><TD style="TEXT-ALIGN: right">24.23</TD><TD style="TEXT-ALIGN: right">23.62</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>13.99 22.42 26.44 26.20 24.78 23.99</TD><TD style="TEXT-ALIGN: right">13.99</TD><TD style="TEXT-ALIGN: right">22.42</TD><TD style="TEXT-ALIGN: right">26.40</TD><TD style="TEXT-ALIGN: right">26.20</TD><TD style="TEXT-ALIGN: right">24.78</TD><TD style="TEXT-ALIGN: right">23.99</TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>13.7 22.9 24.4 26.1 23.5</TD><TD style="TEXT-ALIGN: right">13.70</TD><TD style="TEXT-ALIGN: right">22.90</TD><TD style="TEXT-ALIGN: right">24.40</TD><TD style="TEXT-ALIGN: right">26.10</TD><TD style="TEXT-ALIGN: right">23.50</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>13.7 22.9 24.5 25.0 23.9</TD><TD style="TEXT-ALIGN: right">13.70</TD><TD style="TEXT-ALIGN: right">22.90</TD><TD style="TEXT-ALIGN: right">24.50</TD><TD style="TEXT-ALIGN: right">25.00</TD><TD style="TEXT-ALIGN: right">23.90</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>26.0 24.6 24.6 24.0 24.0</TD><TD style="TEXT-ALIGN: right">26.00</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">24.00</TD><TD style="TEXT-ALIGN: right">24.00</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>14.2 22.6 24.0 23.9 23.2</TD><TD style="TEXT-ALIGN: right">14.20</TD><TD style="TEXT-ALIGN: right">22.60</TD><TD style="TEXT-ALIGN: right">24.00</TD><TD style="TEXT-ALIGN: right">23.90</TD><TD style="TEXT-ALIGN: right">23.20</TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>24.6 23.2 23.8 23.7</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">23.20</TD><TD style="TEXT-ALIGN: right">23.80</TD><TD style="TEXT-ALIGN: right">23.70</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 21px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>24.5 22.3 24.6 23.2</TD><TD style="TEXT-ALIGN: right">24.50</TD><TD style="TEXT-ALIGN: right">22.30</TD><TD style="TEXT-ALIGN: right">24.60</TD><TD style="TEXT-ALIGN: right">23.20</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
2) Extract 4,5 or 6 values depending on the original string in the cell.
3) make the cells with no data blank
The formula I would be looking for would need to be copied and pasted down several thousands of rows, of which are a mixture of 4,5 and 6 values that need to be extracted.
Thanking you in advance for your help.
Last edited:
