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]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
Give this a try:

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"Cheque ",REPT(" ",400)),400))," ",REPT(" ",200)),200)),"#","")
 
Upvote 0
In the same data we have another cell which needs amount + date.

[TABLE="width: 499"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Details[/TD]
[TD]Amount[/TD]
[TD] [/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016][/TD]
[TD="align: right"]1000[/TD]
[TD] [/TD]
[TD="align: right"]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="align: right"]2000[/TD]
[TD] [/TD]
[TD="align: right"]296[/TD]
[/TR]
[TR]
[TD]Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016][/TD]
[TD="align: right"]3000[/TD]
[TD] [/TD]
[TD="align: right"]228[/TD]
[/TR]
[TR]
[TD]Inward Clearing - Cheque #3787 cleared [Reference : 2-1-1-5055-04-May-2016][/TD]
[TD="align: right"]4000[/TD]
[TD] [/TD]
[TD="align: right"]3787[/TD]
[/TR]
[TR]
[TD]Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016][/TD]
[TD="align: right"]5000[/TD]
[TD] [/TD]
[TD="align: right"]4748[/TD]
[/TR]
[TR]
[TD]Cash deposited [Reference : 1-8-6-187-21-May-2016][/TD]
[TD="align: right"]6000[/TD]
[TD] [/TD]
[TD="align: right"]6000210516[/TD]
[/TR]
[TR]
[TD]Cash deposited [Reference : 9-6-8-477-19-May-2016][/TD]
[TD="align: right"]7000[/TD]
[TD] [/TD]
[TD="align: right"]7000190516[/TD]
[/TR]
[TR]
[TD]Cash deposited [Reference : 6-3-9-357-17-May-2016][/TD]
[TD="align: right"]8000[/TD]
[TD] [/TD]
[TD="align: right"]8000170516[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
In the same data we have another cell which needs amount + date.

[TABLE="width: 589"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Date a[/TD]
[TD]Details[/TD]
[TD]Amount[/TD]
[TD] [/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]30-Apr-16[/TD]
[TD]Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016][/TD]
[TD="align: right"]1000[/TD]
[TD] [/TD]
[TD="align: right"]3791[/TD]
[/TR]
[TR]
[TD="align: right"]30-Apr-16[/TD]
[TD]Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016][/TD]
[TD="align: right"]2000[/TD]
[TD] [/TD]
[TD="align: right"]296[/TD]
[/TR]
[TR]
[TD="align: right"]30-Apr-16[/TD]
[TD]Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016][/TD]
[TD="align: right"]3000[/TD]
[TD] [/TD]
[TD="align: right"]228[/TD]
[/TR]
[TR]
[TD="align: right"]4-May-16[/TD]
[TD]Inward Clearing - Cheque #3787 cleared [Reference : 2-1-1-5055-04-May-2016][/TD]
[TD="align: right"]4000[/TD]
[TD] [/TD]
[TD="align: right"]3787[/TD]
[/TR]
[TR]
[TD="align: right"]17-May-16[/TD]
[TD]Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016][/TD]
[TD="align: right"]5000[/TD]
[TD] [/TD]
[TD="align: right"]4748[/TD]
[/TR]
[TR]
[TD="align: right"]21-May-16[/TD]
[TD]Cash deposited [Reference : 1-8-6-187-21-May-2016][/TD]
[TD="align: right"]6000[/TD]
[TD] [/TD]
[TD="align: right"]6000210516[/TD]
[/TR]
[TR]
[TD="align: right"]19-May-16[/TD]
[TD]Cash deposited [Reference : 9-6-8-477-19-May-2016][/TD]
[TD="align: right"]7000[/TD]
[TD] [/TD]
[TD="align: right"]7000190516[/TD]
[/TR]
[TR]
[TD="align: right"]17-May-16[/TD]
[TD]Cash deposited [Reference : 6-3-9-357-17-May-2016][/TD]
[TD="align: right"]8000[/TD]
[TD] [/TD]
[TD="align: right"]8000170516[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
nazeem said:
In the same data we have another cell which needs amount + date.
Click to expand...
It's not clear to me which is the original data and which are the results you want generated. Is it only that final column you want help with? .. or Date, Amount etc?
 
Upvote 0
nazeem said:
In the same data we have another cell which needs amount + date.

[TABLE="width: 589"]
<tbody>[TR]
[TD]Date a
[/TD]
[TD]Details
[/TD]
[TD]Amount
[/TD]
[TD][/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD="align: right"]30-Apr-16
[/TD]
[TD]Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016]
[/TD]
[TD="align: right"]1000
[/TD]
[TD][/TD]
[TD="align: right"]3791
[/TD]
[/TR]
[TR]
[TD="align: right"]30-Apr-16
[/TD]
[TD]Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]
[/TD]
[TD="align: right"]2000
[/TD]
[TD="align: right"]17-May-16
[/TD]
[TD]Cash deposited [Reference : 6-3-9-357-17-May-2016]
[/TD]
[TD="align: right"]8000
[/TD]
[TD][/TD]
[TD="align: right"]8000170516
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: right"]
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD]
[/TD]
[TD="align: right"]
[/TD]
[TD][/TD]
[TD="align: right"]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Does this UDF (user defined function) do what you want...
Code: 
[table="width: 500"]
[tr]
	[td]Function ChequeNum(R As Range) As String
  Dim Num As Variant, V As Variant, Chq As String, Cheques() As String
  Application.Volatile
  Cheques = Split(R.Value, "cheque", , vbTextCompare)
  For Each V In Cheques
    Chq = Replace(Replace(V, "#", ""), " ", "")
    Num = Val(Chq)
    If Num Then
      ChequeNum = Left(Chq, InStr(Chq, Num) + Len(Num) - 1)
      Exit Function
    End If
  Next
  ChequeNum = R.Offset(, 1) & Format(R.Offset(, -1), "ddmmyy")
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ChequeNum just like it was a built-in Excel function. For example, put this formula in cell E2 and copy it down to the end of your data...

=ChequeNum(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
If Rick is correct that it is just the final 'Result' column that you are after, then you could also try this worksheet formula, copied down. It assumes column A are real dates, not Text dates.

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]3000228
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]50004748
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 Date
 
Upvote 0
Rick Rothstein said:
Does this UDF (user defined function) do what you want...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Function ChequeNum(R As Range) As String
  Dim Num As Variant, V As Variant, Chq As String, Cheques() As String
  Application.Volatile
  Cheques = Split(R.Value, "cheque", , vbTextCompare)
  For Each V In Cheques
    Chq = Replace(Replace(V, "#", ""), " ", "")
    Num = Val(Chq)
    If Num Then
      ChequeNum = Left(Chq, InStr(Chq, Num) + Len(Num) - 1)
      Exit Function
    End If
  Next
  ChequeNum = R.Offset(, 1) & Format(R.Offset(, -1), "ddmmyy")
End Function[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use ChequeNum just like it was a built-in Excel function. For example, put this formula in cell E2 and copy it down to the end of your data...

=ChequeNum(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
Click to expand...


Thanks Rick. Learning new things everyday.
 
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,230
Messages
6,170,883
Members
452,364
Latest member
springate

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