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.

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

 

[nazeem]

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.

Extract Date

*	A	B	C	D
Date a	Details
Cheque 3791 encashed [Reference : 0-1-4-314-30-Apr-2016]
Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]
Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016]
Inward Clearing - Cheque #3787 cleared [Reference : 2-1-1-5055-04-May-2016]
Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016]
Cash deposited [Reference : 1-8-6-187-21-May-2016]
Cash deposited [Reference : 9-6-8-477-19-May-2016]
Cash deposited [Reference : 6-3-9-357-17-May-2016]

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:102px;"><col style="width:641px;"><col style="width:64px;"><col style="width:128px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]Amount[/TD]
[TD="align: right"]Result[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]30-Apr-2016[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]3791[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]30-Apr-2016[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]296[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]30-Apr-2016[/TD]

[TD="align: right"]3000[/TD]
[TD="align: right"]228[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]04-May-2016[/TD]

[TD="align: right"]4000[/TD]
[TD="align: right"]3787[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]17-May-2016[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]4748[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]21-May-2016[/TD]

[TD="align: right"]6000[/TD]
[TD="align: right"]6000210516[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]19-May-2016[/TD]

[TD="align: right"]7000[/TD]
[TD="align: right"]7000190516[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]17-May-2016[/TD]

[TD="align: right"]8000[/TD]
[TD="align: right"]8000170516[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =IFERROR(SUBSTITUTE(RIGHT(SUBSTITUTE(LEFT(B2,FIND(" ",B2,8+IFERROR(SEARCH("Cheque #",B2),SEARCH("Cheque ",B2)))-1)," ",REPT(" ",10)),10),"#","")+0,C2&TEXT(A2,"ddmmyy")) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sorry for late reply, I wanted the formula for D2. and It really works. Thanks a lot.

 

[Peter_SSs]

Thanks Rick. Learning new things everyday.

Sorry for late reply, I wanted the formula for D2. and It really works. Thanks a lot.

Good to have choices.

 

[Rick Rothstein]

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.

Spreadsheet Formulas

Cell Formula D2 =IFERROR(SUBSTITUTE(RIGHT(SUBSTITUTE(LEFT(B2,FIND(" ",B2,8+IFERROR(SEARCH("Cheque #",B2),SEARCH("Cheque ",B2)))-1)," ",REPT(" ",10)),10),"#","")+0,C2&TEXT(A2,"ddmmyy")) <tbody> </tbody>

<tbody>
</tbody>

Not sure if it is important to the OP or not, but one difference between my code and your formula (as posted) is that the output from my UDF retains leading 0 (if any) on the cheque numbers whereas the output from your formula does not.

 

[nazeem]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: right"]

30-Apr-2016[/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="align: right"]

000228[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="align: right"]17-May-2016[/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="align: right"]004748[/TD]
[/TR]
</tbody>[/TABLE]

The cheque number shouldnt knock down the initial 00 in the result.

 

[Peter_SSs]

Not sure if it is important to the OP or not, but one difference between my code and your formula (as posted) is that the output from my UDF retains leading 0 (if any) on the cheque numbers whereas the output from your formula does not.

Yes, Rick, I had noted that but went with the desired output given in post #6 (though I do note that had changed since post #1).

My solution would also fail for data like this, but I took the samples provided as representative.
Transfer Cheque processed: 31102-1447200-8-2-2 Cheque 296 [Reference : 2-1-1-44510-30-Apr-2016]


If leading zeros were required, a formula solution may still be possible but to try to get the best one, I would want to seek further information about the data. I'll ask for that if the OP wants such a formula solution.

 

[Peter_SSs]

The cheque number shouldnt knock down the initial 00 in the result.

Would have been best to include those zeros in post #6 then.

If leading zeros were required,..

I hadn't seen post #14 when I posted that.

If you were still interested in a possible worksheet formula solution, would it be possible to have data like this,

- where the text "Cheque " appears two or more times (without a following #)?
Transfer Cheque processed: 31102-1447200-8-2-2 Cheque 296 [Reference : 2-1-1-44510-30-Apr-2016]

- where the text "Cheque #" appears two or more times?
Transfer Cheque # is provided: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]

 

[nazeem]

Hi, Peter, while copying the result i should have been careful, I tried, Ricks code it worked perfectly. Also Peter can you tweak the formula a bit for worksheet formula. That the cheque number if it starts with 0, the formula should still retain the 0's.

 

[Rick Rothstein]

Hi, Peter, while copying the result i should have been careful, I tried, Ricks code it worked perfectly. Also Peter can you tweak the formula a bit for worksheet formula. That the cheque number if it starts with 0, the formula should still retain the 0's.

You need to answer Peter's other questions in Message #16.

 

[nazeem]

If you were still interested in a possible worksheet formula solution, would it be possible to have data like this,

- where the text "Cheque " appears two or more times (without a following #)?
Transfer Cheque processed: 31102-1447200-8-2-2 Cheque 296 [Reference : 2-1-1-44510-30-Apr-2016]

- where the text "Cheque #" appears two or more times?
Transfer Cheque # is provided: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]


When we generate statement from bank we get the below line.

Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]

When transfer cheque comes first cheque doesnt have # in it and the later one has # when number is mentioned but when cheque is encashed # doesnt appear "[TABLE="width: 327"]
<tbody>[TR]
[TD="class: xl65, width: 327"]Cheque 3791 encashed [Reference[/TD]
[/TR]
</tbody>[/TABLE]
in it.

Currently I have listed all the possible bank lines which we get.

Also the amount comes in Two cells one is the credit and other debit which i CONCATENATE then apply the formula. This data is then loaded in the data loader and entered in the software.

 

[Rick Rothstein]

When we generate statement from bank we get the below line.

Transfer Cheque processed: 31102-1447200-8-2-2 Cheque #296 [Reference : 2-1-1-44510-30-Apr-2016]

When transfer cheque comes first cheque doesnt have # in it and the later one has # when number is mentioned but when cheque is encashed # doesnt appear "[TABLE="width: 327"]
<tbody>[TR]
[TD="class: xl65, width: 327"]Cheque 3791 encashed [Reference[/TD]
[/TR]
</tbody>[/TABLE]
in it.

Currently I have listed all the possible bank lines which we get.

My guess is that Peter will be able to post a more efficient formula, but until he does, this appears to work...

=IF(ISNUMBER(SEARCH("Cheque",B2)),TRIM(MID(SUBSTITUTE(IF(ISNUMBER(SEARCH("Cheque #",B2)),TRIM(MID(B2,SEARCH("Cheque #",B2)+8,15)),IF(ISNUMBER(SEARCH("Cheque",B2)),TRIM(MID(B2,SEARCH("Cheque",B2)+6,15))))," ",REPT(" ",300)),1,300)),C2&TEXT(A2,"ddmmyy"))