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)