Remove selected numbers from text & number string

[Tosborn]

Howdy,

Data is typically as such:

25MAR16 CHAMPS FLOWERS HMP WAGGA W 67.00 1,037.00- <colgroup><col width="389"></colgroup><tbody> </tbody>

01APR16 HUNTERS NEWSAGENCY WAGGA W 821.00 1,858.00- <colgroup><col width="389"></colgroup><tbody> </tbody>

14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99- <colgroup><col width="389"></colgroup><tbody> </tbody>

27APR16 GM CABS PTY. LTD. MASCOT 78.75 1,658.75- <colgroup><col width="389"></colgroup><tbody> </tbody>

25MAR16 CHAMPS FLOWERS HMP WAGGA W 67.00 1,037.00- <colgroup><col width="389"></colgroup><tbody> </tbody>

01APR16 HUNTERS NEWSAGENCY WAGGA W 821.00 1,858.00- <colgroup><col width="389"></colgroup><tbody> </tbody>

14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99- <colgroup><col width="389"></colgroup><tbody> </tbody>

13MAY16 HOTEL GRD CHANCELLOR BRISB 200.50 2,014.29- <colgroup><col width="389"></colgroup><tbody> </tbody>

01APR16 BICHER AND SON PL ERMINGTO 1,000.00 2,082.63- <colgroup><col width="389"></colgroup><tbody> </tbody>

<tbody>
</tbody>

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: 14APR16 FOCUS WAGGA WAGGA WAGGA 149.00 153.99- <colgroup><col width="395"></colgroup><tbody> </tbody> use =RIGHT(A5,17) then delivers me the value of GA 149.00 153.99- <colgroup><col width="395"></colgroup><tbody> </tbody> and use the handy formulae provided on this blog:

=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)

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

However this returns the value of: 1490015399 <colgroup><col width="83"></colgroup><tbody> </tbody>

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

<tbody>
</tbody>

 

[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.