Need help in data seperation from text

[nazeem]

I have some bank data, below the reference, can any help in finding the formula that will help get this desired result.

From each cell I need cheque number.

[TABLE="width: 570"]
<tbody>[TR]
[TD][/TD]
[TD] Result[/TD]
[/TR]
[TR]
[TD]Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016][/TD]
[TD] 3791[/TD]
[/TR]
[TR]
[TD]Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016][/TD]
[TD] 296[/TD]
[/TR]
[TR]
[TD]Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016][/TD]
[TD] 000228[/TD]
[/TR]
[TR]
[TD]Inward Clearing - Cheque #3787 cleared [Reference : 2-1-1-5055-04-May-2016][/TD]
[TD] 3787[/TD]
[/TR]
[TR]
[TD]Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016][/TD]
[TD]
004748[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Merchant ID or terminal ID we call it but what we need is the number that will always start with "10" will have 9 Characters.

At least that is a logical 'rule' that we really needed to know from the start.

You said that for inward remittance it is what is after STE up to 12 characters but in the final row of post #25 you have an inward remittance and your result is more than 12 characters.
What if it was "INWARD REMITTANCE ... STE ED HOWES Purc s inuoiceno ahp". Would the result be "ED HOWES Pur"?
You have a result "AUDIT CONFIRMATION" for which you have give no 'rule'.
We appear to now have "CHQ. NO.:" whereas earlier in the thread we had "Cheque" or "Cheque #".

Overall I see this as far too unstructured to have a feasible formula solution.

 

[nazeem]

You said that for inward remittance it is what is after STE up to 12 or 15 characters but in the final row of post #25 you have an inward remittance and your result is more than 12 characters.
What if it was "INWARD REMITTANCE ... STE ED HOWES Purc s inuoiceno ahp". Would the result be "ED HOWES Pur"?

"ED HOWES Pur" or "ED HOWES Purc s" is ok, thats just for reference we need that information.

You have a result "AUDIT CONFIRMATION" for which you have give no 'rule'.

You can exclude the AUDIT part from the formula.

We appear to now have "CHQ. NO.:" whereas earlier in the thread we had "Cheque" or "Cheque #".

The previous bank statement there we needed Cheque or Cheque #
For the new bank statement we have "CHQ.NO"

Don't touch the previous formula or statement or combine both. The previous formula which you have given is perfect for the bank statement

Its different bank statement and the one i posted #25 is new bank statement.

Hope this helps.

 

[nazeem]

Dear Peter,

Any update for the formula.

 

[Peter_SSs]

Dear Peter,

Any update for the formula.

No, sorry, I haven't been able to get around a workable formula.

 

[nazeem]

No, sorry, I haven't been able to get around a workable formula.

It would be helpful if you can find a formula for these lines.

[TABLE="width: 1218"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Particulars[/TD]
[TD]Amount[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2016[/TD]
[TD]SB-TRANSFER 44342960 SB REF. 7038594 B/O NETWORK INTERNATIONAL LLC NI POS SETT 010116 109866251 Value DATE, 02/01/2016 - S11702254[/TD]
[TD="align: right"]619.6[/TD]
[TD="align: right"]109866251[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2016[/TD]
[TD]SB-TRANSFER 44342961 SB REF. 7038594 B/O NETWORK INTERNATIONAL LLC NI POS SETT 010116 109866269 Value DATE, 02/01/2016 - S11702259[/TD]
[TD="align: right"]3,710.82[/TD]
[TD="align: right"]109866269[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2016[/TD]
[TD]SB-TRANSFER 44366043 SB REF. 7041502 B/O NETWORK INTERNATIONAL LLC NI POS SETT 020116 109866079 Value DATE, 03/01/2016 - S12114960[/TD]
[TD="align: right"]1,055.01[/TD]
[TD="align: right"]109866079[/TD]
[/TR]
[TR]
[TD="align: right"]10/1/2016[/TD]
[TD]003769 INWARD CLEARING CHQ. NO:003769 Value DATE, 10/01/2016 - S14981635[/TD]
[TD="align: right"]1,365.00[/TD]
[TD="align: right"]3769[/TD]
[/TR]
[TR]
[TD]16/01/2016[/TD]
[TD]003770 INWARD CLEARING CHQ. NO:003770 Value DATE, 16/01/2016 - S17213951[/TD]
[TD="align: right"]2,865.00[/TD]
[TD="align: right"]3770[/TD]
[/TR]
[TR]
[TD]23/01/2016[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 23/01/2016 - EN21338[/TD]
[TD="align: right"]1,505.00[/TD]
[TD="align: right"]1505230116[/TD]
[/TR]
[TR]
[TD]25/01/2016[/TD]
[TD]CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 25/01/2016 - EN34426[/TD]
[TD="align: right"]1,100.00[/TD]
[TD="align: right"]1100250116[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2016[/TD]
[TD]SB-TRANSFER 44352901 SB REF. 7038604 B/O NETWORK INTERNATIONAL LLC DCC REVENUE 311215 109943035 Value DATE, 02/01/2016 - S11718081[/TD]
[TD="align: right"]11.23[/TD]
[TD="align: right"]109943035[/TD]
[/TR]
</tbody>[/TABLE]

 

[nazeem]

Dear Peter,

The data posted #35, can you try this one, it would really helpful.