Remove selected numbers from text & number string

[Tosborn]

Howdy,

Data is typically as such:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]25MAR16 CHAMPS FLOWERS HMP WAGGA W 67.00 1,037.00-
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]01APR16 HUNTERS NEWSAGENCY WAGGA W 821.00 1,858.00-
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]27APR16 GM CABS PTY. LTD. MASCOT 78.75 1,658.75-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]25MAR16 CHAMPS FLOWERS HMP WAGGA W 67.00 1,037.00-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]01APR16 HUNTERS NEWSAGENCY WAGGA W 821.00 1,858.00-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99-
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]13MAY16 HOTEL GRD CHANCELLOR BRISB 200.50 2,014.29-[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 389"]
<colgroup><col width="389"></colgroup><tbody>[TR]
[TD="class: xl65, width: 389"]01APR16 BICHER AND SON PL ERMINGTO 1,000.00 2,082.63-
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

<colgroup><col style="mso-width-source:userset;mso-width-alt:14226;width:292pt" width="389"> </colgroup><tbody>
[TD="width: 389"]

What I need to do is remove only the 2nd last set of numbers in the string. For example in the first cell I need 67.00, and in the 2nd cell I need 821.00 and so on.
As you can see sometimes the number is a low number (67.00) or sometimes is a higher number (1,000.00). Thus causing difficulties in using just a RIGHT formulae.

I've tried to first trim the data down a bit, say for example:

[TABLE="width: 395"]
<colgroup><col width="395"></colgroup><tbody>[TR]
[TD="width: 395"]14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99-[/TD]

</tbody>

use =RIGHT(A5,17) then delivers me the value of

[TABLE="width: 395"]
<colgroup><col width="395"></colgroup><tbody>[TR]
[TD="width: 395"]GA 149.00 153.99-
[/TD]
[/TR]
</tbody>[/TABLE]


and use the handy formulae provided on this blog:
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 389"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]However this returns the value of:

[TABLE="width: 83"]
<colgroup><col width="83"></colgroup><tbody>[TR]
[TD="width: 83, align: right"]1490015399
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]
Which doesn't really help as I still can't 'chop' the number down as sometimes the numbers will be larger or smaller on either side.


Any ideas? I'm giving away free virtually coffees for a whizbang formulae You'll be virtually awake, not sure that'll help though.

Thanks,
Tim


[/TD]
[/TR]
</tbody>[/TABLE]

 

[WarPigl3t]

Using the below raw data:
25MAR16 CHAMPS FLOWERS HMP WAGGA W 67.00 1,037.00-

Please reply with what it should look like after the formula I provide. Your explanation is confusing. This is what I think the data should look like after the formula based on what you wrote:
25MAR16 CHAMPS FLOWERS HMP WAGGA W 1,037.00-

 

[Peter_SSs]

What I need to do is remove only the 2nd last set of numbers in the string. For example in the first cell I need 67.00, and in the 2nd cell I need 821.00 and so on.

Welcome to the MrExcel board!

See if this, copied down, is what you want.

Excel Workbook
	A	B
1	25MAR16 CHAMPS FLOWERS HMP WAGGA W 67.00 1,037.00-	67
2	01APR16 HUNTERS NEWSAGENCY WAGGA W 821.00 1,858.00-	821
3	14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99-	149
4	27APR16 GM CABS PTY. LTD. MASCOT 78.75 1,658.75-	78.75
5	25MAR16 CHAMPS FLOWERS HMP WAGGA W 67.00 1,037.00-	67
6	01APR16 HUNTERS NEWSAGENCY WAGGA W 821.00 1,858.00-	821
7	14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99-	149
8	13MAY16 HOTEL GRD CHANCELLOR BRISB 200.50 2,014.29-	200.5
9	01APR16 BICHER AND SON PL ERMINGTO 1,000.00 2,082.63-	1000
Extract 2nd last

 

[Tosborn]

Hi all,

Apologies if my question was a bit confusing.

Peter, fantastic - that is it!!

Love my Mr. Excel blog!!

Cheers,

Tim

 

[Peter_SSs]

You are very welcome.