extracting numbers from text strings after change in conditions

[chrissooo]

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.

 

[AlphaFrog]

Have you considered using the Text to Columns feature under the Data menu and use a space as the delimiter?

 

[chrissooo]

Hi AlphaFrog

I have considered that, but this is part of a huge ss that draws data down from an Access db.

I clear contents in column AV when the next set of data is imported.

So, I would like a formula based solution.

 

[AlphaFrog]

Put this in AW7 and copy it across and down as needed.

=IF(LEN($AV7)-LEN(SUBSTITUTE($AV7," ",""))< COLUMN(A$1)-1,"",
   TRIM(MID(SUBSTITUTE($AV7," ",REPT(" ",100)),COLUMN(A$1)*100-99,100))*1)

 

[Peter_SSs]

I would still consider Text to Columns. It only takes a few moments to do manually, or it could be done by a macro when the data is imported.

Is the data imported manually, or by macro?

However, this can be done by formula. Using a helper row as I have done makes the formulas simpler though it could still be done without the helper row. The formula in AW7 is copied across and down. My results are text. If you need them to be numeric, then add "+0" to the end of the formula before copying it to the other columns/rows.

Also, not sure if you are aware that you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board. Note that I have shown just 1 of 70 formulas that are in the part of my sheet I have shown below.

Excel Workbook
	AV	AW	AX	AY	AZ	BA	BB	BC
5		1	2	3	4	5	6	7
6
7	13.80 22.89 25.12 25.28 23.80	13.80	22.89	25.12	25.28	23.80
8	14.16 22.03 23.69 24.23 23.62	14.16	22.03	23.69	24.23	23.62
9	13.99 22.42 26.44 26.20 24.78 23.99	13.99	22.42	26.44	26.20	24.78	23.99
10	13.7 22.9 24.4 26.1 23.5	13.7	22.9	24.4	26.1	23.5
11	13.7 22.9 24.5 25.0 23.9	13.7	22.9	24.5	25.0	23.9
12	26.0 24.6 24.6 24.0 24.0	26.0	24.6	24.6	24.0	24.0
13	14.2 22.6 24.0 23.9 23.2	14.2	22.6	24.0	23.9	23.2
14	24.6 23.2 23.8 23.7	24.6	23.2	23.8	23.7
15	24.5 22.3 24.6 23.2	24.5	22.3	24.6	23.2
16
Split Data

 

[Peter_SSs]

If you need them to be numeric, then add "+0" to the end of the formula before copying it to the other columns/rows.

Oops, that would need a column check along the lines of AlphaFrogs formula (which I had not seen when I made my post). However, I would use a slightly different column check as that one will return incorrect results if subsequently new column(s) are added to the left of column A.

Perhaps this ..
=IF(LEN($AV7)-LEN(SUBSTITUTE($AV7," ",""))< AW$5-1,"",TRIM(MID(SUBSTITUTE($AV7," ",REPT(" ",100)),100*(COLUMNS($AW7:AW7)-1)+1,100))+0)

.. or if you have Excel 2007+
=IFERROR(TRIM(MID(SUBSTITUTE($AV7," ",REPT(" ",100)),100*(AW$5-1)+1,100))+0,"")

 

[chrissooo]

Hello

Alpha and Peter.

Sorry, I didn't make it clear from the outset (though it may have been able to be deduced from my rather simplistic formulas), that there are 2 spaces between each set of numbers. I gather that is why both formulas aren't working??? I get a #VALUE! error in columns AX, AZ, BB....the value in AY should be in AX, BA in AY etc

Peter, the data is imported via a macro and I will give the restricting formulas feature a go on my next post if required. Thanks.

Could you please amend the formula guys so I can get this working?

Thanks again.

 

[AlphaFrog]

If your data is imported via a macro, that same macro could easily be set up to clear the previous data and then use Text to Columns to split the new data into multiple columns. That would be far more efficient than using formulas.

I know i didn't say that in my original post but it could have been deduced.

Also, what version of Excel?

 

[chrissooo]

I'm using 2007.

 

[Peter_SSs]

If your data is imported via a macro, that same macro could easily be set up to clear the previous data and then use Text to Columns to split the new data into multiple columns. That would be far more efficient than using formulas.

I'm still agreeing with that also, but if you are insisting on a formula then, depending on whether text values will suffice or you need numeric values (you still haven't said):

Text values sufficient:
Amend my formula from post #5 by adding the red bits
=TRIM(MID(SUBSTITUTE(TRIM($AV7)," ",REPT(" ",100)),100*(AW$5-1)+1,100))

Numerica values required:
Amend my formula from post #6 by adding the same red bits
=IFERROR(TRIM(MID(SUBSTITUTE(TRIM($AV7)," ",REPT(" ",100)),100*(AW$5-1)+1,100))+0,"")