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.

	Result
Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016]	3791
Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]	296
Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016]	000228
Inward Clearing - Cheque #3787 cleared [Reference : 2-1-1-5055-04-May-2016]	3787
Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016]	004748

<tbody>
</tbody>

 

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

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

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[nazeem]

Dear Peter,

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