• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
DEOWB_LOGO_192X192

DEO West Bengal

The DEO Education Channel

  • Home
  • New Updates
  • Tutorials
  • ORDER
  • PAYMENTS
  • ISGP
  • UTILITY
  • Videos
You are here: Home / TUTORIAL / Extract Scheme Code

Extract Scheme Code

February 8, 2017 by Subhrapratim De

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

 

Filed Under: TUTORIAL, UTILITY

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Footer

About Us

DEOWB (Data Entry Operators - West Bengal) was started in 2009 as deowestbengal.wordpress.com to help all the DEO/VLEs working under MGNREGA schemes. With times things have changed to this. If you are a DEO/VLE, you are at the right place. This is the ultimate DEO Education Channel..!!!

Social

  • Facebook
  • YouTube

© 2009–2023 DEO West Bengal