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]

I think this fairly small change to my previous formula may do the trick.

Excel Workbook
	A	B	C	D
1	Date a	Details	Amount	Result
2	30-Apr-2016	Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016]	1000	3791
3	30-Apr-2016	Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]	2000	296
4	30-Apr-2016	Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016]	3000	000228
5	04-May-2016	Inward Clearing - Cheque #3787 cleared [Reference : 2-1-1-5055-04-May-2016]	4000	3787
6	17-May-2016	Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016]	5000	004748
7	21-May-2016	Cash deposited [Reference : 1-8-6-187-21-May-2016]	6000	6000210516
8	19-May-2016	Cash deposited [Reference : 9-6-8-477-19-May-2016]	7000	7000190516
9	17-May-2016	Cash deposited [Reference : 6-3-9-357-17-May-2016]	8000	8000170516
Extract Chq Num

 

[nazeem]

Thanks Peter. It works.

 

[Rick Rothstein]

Thanks Peter. It works.

See, I told you (Message #20) that Peter will be able to post a more efficient formula.

 

[Peter_SSs]

Thanks Peter. It works.

You are welcome.

 

[nazeem]

Dear Peter,
Dear Rick,

Thanks for your effort. and sorry to bother you. I have one more data that need data separation.

			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
26/01/2016	INTEREST COLLECTION 1011089602301:Int.Coll: to Interest run Value DATE, 26/01/2016 - S21296807	1,388.72	INTEREST
14/02/2016	INWARD REMITTANCE PO/178840503/101 STE RAHUL VERMA Purc s inuoiceno ahp1 hasing Goods perfumes inuoiceno ahp Value DATE, 14/02/2016 - S29057445	1,554.00	RAHUL VERMA
6/3/2016	003779 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003779 Value DATE, 06/03/2016 - EN25616	6,000.00	3779
15/03/2016	003784 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003784 Value DATE, 15/03/2016 - EN28709	3,000.00	3784
24/03/2016	TRANSFER M2946135 AUDIT CONFIRMATION CHARGES Value DATE, 24/03/2016 - S45423915	150	AUDIT CONFIRMATION
14/05/2016	ACCOUNT TRANSFER 003791 4677450300139020 CREDIT CARD PYMT CHQ. NO:003791 Value DATE, 14/05/2016 - EN21259	6,000.00	3791
14/05/2016	TRANSFER 2016051300121706 IPOEBI2016H 1556 USD1829@3.67 Value DATE, 14/05/2016 - EN57524	6,712.43	USD 18293.67
31/05/2016	INWARD REMITTANCE PO/185300138/100 STE ALBERT JOHNSON Pers om onal remittances by foreign employe Value DATE, 30/05/2016 - S74670327	2,750.00	ALBERT JOHNSON

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

 

[Peter_SSs]

Dear Peter,
Dear Rick,

Thanks for your effort. and sorry to bother you. I have one more data that need data separation.

Should we just guess how to determine what should go in the result column, or might you provide some logic for what goes there?

 

[nazeem]

Should we just guess how to determine what should go in the result column, or might you provide some logic for what goes there?

Its the same as previous data only thing is there some card transaction for which we need the merchant ID of the transaction i.e. 109866251 to be entered.

Correction. In result USD 1829 it should be not USD 18293.67.

Important aspect in the result column are as below. They are very repetitive.

Merchant ID POS : 109866251
Cheque clearing : 3769
Amount plus date : 1505230116
Interest part comes once a month:
Audit Confirmation once a year:
Inward remittance comes twice a month:

hope this clarifies. Accordingly you can prioritize the formula.

 

[nazeem]

Revised sheet with one addition "DCC REVENUE"

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.60	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	3710.82	109866269
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
10/1/2016	003769 INWARD CLEARING CHQ. NO:003769 Value DATE, 10/01/2016 - S14981635	1365.00	3769
16/01/2016	003770 INWARD CLEARING CHQ. NO:003770 Value DATE, 16/01/2016 - S17213951	2865.00	3770
23/01/2016	CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 23/01/2016 - EN21338	1505.00	1505230116
25/01/2016	CASH RECEIPT CASH DEPOSIT- CASH DEPOSIT- Value DATE, 25/01/2016 - EN34426	1100.00	1100250116
26/01/2016	INTEREST COLLECTION 1011089602301:Int.Coll: to Interest run Value DATE, 26/01/2016 - S21296807	1388.72	INTEREST
14/02/2016	INWARD REMITTANCE PO/178840503/101 STE RAHUL VERMA Purc s inuoiceno ahp1 hasing Goods perfumes inuoiceno ahp Value DATE, 14/02/2016 - S29057445	1554.00	RAHUL VERMA
6/3/2016	003779 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003779 Value DATE, 06/03/2016 - EN25616	6000.00	3779
15/03/2016	003784 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003784 Value DATE, 15/03/2016 - EN28709	3000.00	3784

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Peter_SSs]

hope this clarifies.

Not at all.
How do you determine that a merchant ID is 109866251 and not 44342960 or S11702254 or 010116

How do you determine that INTEREST is a merchant ID? or RAHUL VERMA? or USD1829? etc

I can see no logical connection between the original data and the result column.

 

[nazeem]

Not at all.
How do you determine that a merchant ID is 109866251 and not 44342960 or S11702254 or 010116

How do you determine that INTEREST is a merchant ID? or RAHUL VERMA? or USD1829? etc

I can see no logical connection between the original data and the result column.

How do you determine that a merchant ID is 109866251 and not 44342960 or S11702254 or 010116

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.

How do you determine that INTEREST is a merchant ID? or RAHUL VERMA? or USD1829? etc

Interest or Rahul Verma or USD1829 isnt a merchant ID. They are just reference for your purpose to know the transaction.
For interest the cell with start with interest.
For inward remittance after STE whatever information upto 12 character.
As far as USD part is concerned you need not include in the formula, its once a year transaction.