Extract Scheme Code

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)