How to take a Cheque no from a given statement

[VDS]

@Dear All,

While downloading data from Tally, I received a statement of accounts as per attachment. Here, The cheue no and amount is to be extracted in a separate column. I used Left and Right function to take the cheque no alone. But this is a lengthy process. How to simply this ? The suggested format is highlighted in Turquoise Colour. Even the amount is to be taken separately. This is a voluminous data and makes time consuming.

How to to do this. Your help is requested.

VDS

 

[Somendra Misra]

Hi VDS,

See the attached file.

Regards,

 

[Debraj]

Another "SuggestToAvoid" approach..

Select two continious vertical cell.. (F29,G29)
Activate F29,
Paste below formula..
and Apply using Ctrl + Shift + Enter..

=MID(C29&"",SEARCH({"Cheque No.:","INR"},C29&" ")+LEN({"Cheque No.:","INR"}),(SEARCH(" ",C29&" ",SEARCH({"Cheque No.:","INR"},C29&" ")+LEN({"Cheque No.:","INR"})+1))-(SEARCH({"Cheque No.:","INR"},C29&" ")+LEN({"Cheque No.:","INR"})))*1

 

[VDS]

@Somendra / Debraj,

My goodness. Let me try with something. You people are super king.


VDS

 

[VDS]

@Somendra,

This function is nice & beautiful. But, the cheque No is to extracted at a different place. For that, I will revert shortly as criteria and conditions are little bit different.


VDS

 

[SirJB7]

Hi, VDS!

Try this:
F28: =SI.ERROR(IZQUIERDA(ESPACIOS(REEMPLAZAR(E28;1;HALLAR("Cheque No.:";E28)+LARGO("Cheque No.:")-1;""))&" ";HALLAR(" ";ESPACIOS(REEMPLAZAR(E28;1;HALLAR("Cheque No.:";E28)+LARGO("Cheque No.:")-1;""))&" ")-1);"") -----> in english: =IFERROR(LEFT(TRIM(REPLACE(E28,1,SEARCH("Cheque No.:",E28)+LEN("Cheque No.:")-1,""))&" ",SEARCH(" ",TRIM(REPLACE(E28,1,SEARCH("Cheque No.:",E28)+LEN("Cheque No.:")-1,""))&" ")-1),"")

And in case you're gonna ask how it works, it uses the same technique as described here, except for the TRIM function and the replacement of the comma by the space:
http://chandoo.org/forum/threads/help-extracting-substrings-from-cell-text-with-3-edge-cases.16206/

Regards!

 

[VDS]

@Sir JB7,

Thank you. Will try and give status.


VDS

 

[VDS]

@Somendra / Sir JB7

While going through data, one more issue come to light. Here, the amounts are not in comma. In fact this is converted from pdf to xls. Data is attached. RTrim and LTrim not working. Even the number format has no efect.
How to convert the same into number format

VDS

 

[Somendra Misra]

@VDS

In D4 use below formula and copy down,

=IF(B4>0,SUBSTITUTE(B4,".","",1)+0,"")

Regards,

 

[VDS]

@Thanks a lot. It is done.

VDS