Need help in data seperation from text

N

nazeem

New Member
Joined
May 26, 2016
Messages
27
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]
 
Sort by date Sort by votes
I think this fairly small change to my previous formula may do the trick.

Excel Workbook
ABCD
1Date aDetailsAmountResult
230-Apr-2016Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016]10003791
330-Apr-2016Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]2000296
430-Apr-2016Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016]3000000228
504-May-2016Inward Clearing - Cheque #3787 cleared [Reference : 2-1-1-5055-04-May-2016]40003787
617-May-2016Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016]5000004748
721-May-2016Cash deposited [Reference : 1-8-6-187-21-May-2016]60006000210516
819-May-2016Cash deposited [Reference : 9-6-8-477-19-May-2016]70007000190516
917-May-2016Cash deposited [Reference : 6-3-9-357-17-May-2016]80008000170516
Extract Chq Num
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Dear Peter,
Dear Rick,

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


[TABLE="width: 980"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]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]619.6[/TD]
[TD="align: right"]109866251[/TD]
[/TR]
[TR]
[TD]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]3,710.82[/TD]
[TD="align: right"]109866269[/TD]
[/TR]
[TR]
[TD]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]1,055.01[/TD]
[TD="align: right"]109866079[/TD]
[/TR]
[TR]
[TD]10/1/2016[/TD]
[TD]003769 INWARD CLEARING CHQ. NO:003769 Value DATE, 10/01/2016 - S14981635[/TD]
[TD]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]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]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]1,100.00[/TD]
[TD="align: right"]1100250116[/TD]
[/TR]
[TR]
[TD]26/01/2016[/TD]
[TD]INTEREST COLLECTION 1011089602301:Int.Coll: to Interest run Value DATE, 26/01/2016 - S21296807[/TD]
[TD]1,388.72[/TD]
[TD]INTEREST[/TD]
[/TR]
[TR]
[TD]14/02/2016[/TD]
[TD]INWARD REMITTANCE PO/178840503/101 STE RAHUL VERMA Purc s inuoiceno ahp1 hasing Goods perfumes inuoiceno ahp Value DATE, 14/02/2016 - S29057445[/TD]
[TD]1,554.00[/TD]
[TD]RAHUL VERMA[/TD]
[/TR]
[TR]
[TD]6/3/2016[/TD]
[TD]003779 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003779 Value DATE, 06/03/2016 - EN25616[/TD]
[TD]6,000.00[/TD]
[TD="align: right"]3779[/TD]
[/TR]
[TR]
[TD]15/03/2016[/TD]
[TD]003784 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003784 Value DATE, 15/03/2016 - EN28709[/TD]
[TD]3,000.00[/TD]
[TD="align: right"]3784[/TD]
[/TR]
[TR]
[TD]24/03/2016[/TD]
[TD]TRANSFER M2946135 AUDIT CONFIRMATION CHARGES Value DATE, 24/03/2016 - S45423915[/TD]
[TD]150[/TD]
[TD]AUDIT CONFIRMATION[/TD]
[/TR]
[TR]
[TD]14/05/2016[/TD]
[TD]ACCOUNT TRANSFER 003791 4677450300139020 CREDIT CARD PYMT CHQ. NO:003791 Value DATE, 14/05/2016 - EN21259[/TD]
[TD]6,000.00[/TD]
[TD="align: right"]3791[/TD]
[/TR]
[TR]
[TD]14/05/2016[/TD]
[TD]TRANSFER 2016051300121706 IPOEBI2016H 1556 USD1829@3.67 Value DATE, 14/05/2016 - EN57524[/TD]
[TD]6,712.43[/TD]
[TD]USD 18293.67[/TD]
[/TR]
[TR]
[TD]31/05/2016[/TD]
[TD]INWARD REMITTANCE PO/185300138/100 STE ALBERT JOHNSON Pers om onal remittances by foreign employe Value DATE, 30/05/2016 - S74670327[/TD]
[TD]2,750.00[/TD]
[TD]ALBERT JOHNSON[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
nazeem said:
Dear Peter,
Dear Rick,

Thanks for your effort. and sorry to bother you. I have one more data that need data separation.
Click to expand...
Should we just guess how to determine what should go in the result column, or might you provide some logic for what goes there?
 
Upvote 0
Peter_SSs said:
Should we just guess how to determine what should go in the result column, or might you provide some logic for what goes there?
Click to expand...

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.

 
Upvote 0
Revised sheet with one addition "DCC REVENUE"

[TABLE="width: 964"]
<colgroup><col><col><col span="2"></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.60[/TD]
[TD] 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"]3710.82 [/TD]
[TD]109866269[/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]
[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"]1365.00[/TD]
[TD] 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"]2865.00 [/TD]
[TD]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"]1505.00[/TD]
[TD] 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"]1100.00 [/TD]
[TD]1100250116[/TD]
[/TR]
[TR]
[TD]26/01/2016[/TD]
[TD]INTEREST COLLECTION 1011089602301:Int.Coll: to Interest run Value DATE, 26/01/2016 - S21296807[/TD]
[TD="align: right"]1388.72[/TD]
[TD] INTEREST[/TD]
[/TR]
[TR]
[TD]14/02/2016[/TD]
[TD]INWARD REMITTANCE PO/178840503/101 STE RAHUL VERMA Purc s inuoiceno ahp1 hasing Goods perfumes inuoiceno ahp Value DATE, 14/02/2016 - S29057445[/TD]
[TD="align: right"]1554.00[/TD]
[TD] RAHUL VERMA[/TD]
[/TR]
[TR]
[TD="align: right"]6/3/2016[/TD]
[TD]003779 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003779 Value DATE, 06/03/2016 - EN25616[/TD]
[TD="align: right"]6000.00[/TD]
[TD] 3779[/TD]
[/TR]
[TR]
[TD]15/03/2016[/TD]
[TD]003784 TRANSFER To 135550255100002-4677450300139020 CREDIT CARD PYMT CHQ. NO:003784 Value DATE, 15/03/2016 - EN28709[/TD]
[TD="align: right"]3000.00 [/TD]
[TD]3784[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 964"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
nazeem said:
hope this clarifies.
Click to expand...
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.
 
Upvote 0
Peter_SSs said:
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.
Click to expand...

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
536
skyh3ck
skyh3ck
V
  • Question Question
Need to seperate data of different length in separate columns
Replies
3
Views
1K
CWatts
CWatts
S
  • Question Question
Finding Text and Dates in strings
Replies
0
Views
944
shuaibqayum
S
DayDay
  • Question Question
Read as text from a data dump
Replies
3
Views
1K
DayDay
DayDay

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top