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>

 

[István Hirsch]

Give this a try:

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"Cheque ",REPT(" ",400)),400))," ",REPT(" ",200)),200)),"#","")

 

[nazeem]

Give this a try:

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"Cheque ",REPT(" ",400)),400))," ",REPT(" ",200)),200)),"#","")

Thanks a Lot. Seems it works. I am new to this forum, hope to learn more and contribute.

 

[István Hirsch]

You are welcome, thanks for the feedback.

 

[nazeem]

In the same data we have another cell which needs amount + date.

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

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

 

[nazeem]

In the same data we have another cell which needs amount + date.

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

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

 

[Peter_SSs]

In the same data we have another cell which needs amount + date.

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?

 

[Rick Rothstein]

In the same data we have another cell which needs amount + date.

Date a	Details	Amount		Result
30-Apr-16	Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016]	1000		3791
30-Apr-16	Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]	2000	17-May-16	Cash deposited [Reference : 6-3-9-357-17-May-2016]	8000		8000170516

<tbody>
</tbody>

Does this UDF (user defined function) do what you want...

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

 

[Peter_SSs]

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
	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	228
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	4748
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 Date

 

[nazeem]

Does this UDF (user defined function) do what you want...

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

Thanks Rick. Learning new things everyday.