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>

 

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

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

30-Apr-2016

Return Of Outward Clearing Cheque #000228 From Bank [Reference : 2-1-1-27452-30-Apr-2016]

3000

000228

<tbody>
</tbody>

17-May-2016

Clearance Of Outward Clearing Cheque #004748 From Bank [Reference : 2-1-1-18882-17-May-2016]

5000

004748

<tbody>
</tbody>

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 "

Cheque 3791 encashed [Reference

<tbody>
</tbody>

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 "

Cheque 3791 encashed [Reference

<tbody>
</tbody>

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"))