Extracting scheme code from the mixed content as presented in the MGNREGA portal is a complex job. But is required for many situations like preparing reports for audit and so. So, here I am presenting am MS Excel formula that will do this job.
What is being done here is as follows :
- Copying the data from the MGNREGA report to the excel sheet and using the match destination formatting.
- As the scheme codes are going in the next row its pulled to the upper row in the next column using a simple formula.
- That formula is removed.
- All the sheet data is filtered and sorted by SL No column.
- Now all scheme codes are at same row with the scheme names.
- Unnecessary rows are deleted from bottom.
- A large formula is applied to remove the leading and trailing brackets.
- This formula will also extract the scheme code from those reports where scheme code is in same line with scheme name. Like “Re excavation of X pond (32xxxxxxxx/WH/00000000001)”
Here is the Formula that is used in Column I in the video.
=LEFT("3"&RIGHT(H2,LEN(H2)-SEARCH("(3",H2)-LEN("(3")+1),SEARCH(")","3"&RIGHT(H2,LEN(H2)-SEARCH("(3",H2)-LEN("(3")+1))-1)
Subhrapratim De
Subhrapratim De is a Data Entry Operator (DEO/VLE) under MGNREGA Scheme. He is working at the ground level (Gram Panchayat) since 2008 in West Bengal. He has strong background knowledge of computer system, network and operating system. He has knowledge of programming and databases too. Lastly he is a part time blogger.
