Name Sampling Design Worksheet: Fill & Download for Free

The most flexible way of extracting a substring between delimiters is with a worksheet formula and user-defined function. To return the second field from cell A1 when a semicolon is the delimiter, you might use a formula like:=Splitter(A1,";",2) This formula uses the Splitter function, which should be stored in a regular VBA module sheet just like a recorded macro.Function Splitter(Text As String, Delimiter As String, Optional FieldNumber As Long = 1) As String On Error Resume Next Splitter = Split(Text, Delimiter)(FieldNumber - 1) 'Split returns a zero-based array, so need to return element FieldNumber-1 On Error GoTo 0 End Function It is possible to build a worksheet formula to return the substring, but making the formula handle the general case results in a long and difficult to understand formula.If your substrings are fairly short and there aren’t too many of them, you can use the following. Like the preceding problem, the formula assumes the text is in cell A1 and that a semicolon is used as the delimiter. The formula is designed to go in cell P1, and as you copy it across COLUMNS($P1:P1) returns the numbers 1, 2, 3 to return the first, second and third substrings. The formula starts by replacing each delimiter with a hundred of them. You then look for the desired substring by extracting the nth block of 100 characters and removing the delimiters.=SUBSTITUTE(MID(SUBSTITUTE($A1,";",REPT(";",100)),(COLUMNS($P1:P1)-1)*100+1,100),";","") I recently learned a general way of extracting the nth substring in a string with delimiters. Though it works well, the method used really abuses the FILTERXML function, as the sample text has nothing to do with XML.XML text uses tokens (class names inside angle braces) at the start and end of text. You may have a hierarchy of them. Suppose for example we name the top level r and the second level n, which I imagine reference root and node. <r> is the start and </r> is the end. The text in cell A3 might look like <r>Top level here<n>dog</n><n>cat</n></r>. You want to extract “cat”, and can do it using =INDEX(FILTERXML(A3,"//r/n"),2)The trick is to convert the delimited string into something that looks like XML. You may do that by concatenating the starting and ending tokens, and using SUBSTITUTE to convert the delimiters into a token end followed by token start.I put my test text in cell A1 (dog;horse;goat;ox) and put the formula shown below in cell E1 and copied it across to return the four animals in successive cells. The formula returns an empty string when the list is exhausted.=IFERROR(INDEX(FILTERXML("<r><n>" & SUBSTITUTE($A1,";","</n><n>") & "</n></r>","//r/n"),COLUMNS($E1:E1)),"") 

Pretty much anything you can dream in Excel is possible one way or another.Here is some sample code that I tested in Excel 2013. It will create a new workbook, save it as .xlsm, add some data to a worksheet in that workbook, add a regular code module and put some code in it. Pay particular note to the comments at the beginning of the code block, as my code won't work if you don't set a reference and trust access to VBA project as described in those comments.'For complete discussion of "Programming the VBA Editor" see Chip Pearson's Programming In The VBA Editor 'Must add reference for VBA Extensibility. _  In VBA IDE, open Tools...References menu item. _  Select Microsoft Visual Basic for Applications Extensibility 5.3 then check its box 'Must Trust Access to Visual Basic project. _  Open the Developer...Macro Security...Macro Settings dialog from the ribbon _  Check the box for "Trust access to the VBA project object model" Sub Master() Dim wbNew As Workbook, wb As Workbook Dim flPath As String, flName As String Dim vbComp As VBComponent Set wb = ActiveWorkbook flPath = wb.Path flName = "NewWorkbook.xlsm" Set wbNew = NewXLSM(flPath, flName) AddData wbNew.Worksheets(1) Set vbComp = CreateModule(wbNew, "Module1") AddCode vbComp End Sub  Function NewXLSM(sPath As String, flName As String) As Workbook 'Creates a new workbook, then saves it as .xlsm to a designated path and file name Dim wb As Workbook Dim flPathName As String Set wb = Workbooks.Add flPathName = sPath & IIf(Right(sPath, 1) = Application.PathSeparator, "", Application.PathSeparator) & flName If LCase(Right(flPathName, 5)) <> ".xlsm" Then flPathName = flPathName & ".xlsm" wb.SaveAs flPathName, FileFormat:=52 'Save as .xlsm Set NewXLSM = wb End Function  Sub AddCode(vbMod As VBComponent) Dim sCode As String sCode = "Sub HelloWorld()" & vbLf & "MsgBox ""Hello World""" & vbLf & "End Sub" vbMod.CodeModule.AddFromString sCode End Sub  Sub AddData(ws As Worksheet) 'Shows three different ways to add data to a worksheet With ws  .Range("A1").Value = "Some data"  .[A2].Value = 3  .Cells(3, 1).Formula = "=A2+4" End With End Sub  Function CreateModule(wb As Workbook, ModuleName As String) As VBComponent Dim vbComp As VBComponent With wb.VBProject  Set vbComp = .VBComponents.Add(vbext_ct_StdModule)  Page on vbcomp.name = "Module3" 'ModuleName  Set CreateModule = vbComp End With End Function 

I don’t know about institutions in Jamshedpur. But I would generally say that you should not go with ‘brand name’ institutions. Instead look at the actual teachers who will be teaching you. Many institutions will not tell you who will be teaching. They have many batches and one good teacher. So only one batch gets the good teacher and the others end up with terrible teachers. So ask around past students in the area and get detailed feedback on whether the teachers are good and whether they teach well. If the past students say “so-so teachers but good materials, etc”, that means the teachers were not useful. Don’t join a class just for the materials - you can always get the materials from someone else or usually you can buy it in the market.For IIT JEE or NEET preparation there are five important steps:Learning the concepts: By this I don’t mean just knowing the formulas or definition. Really understanding the concepts and being able to apply it to a situation.Building Problem Solving Skills: Learning good techniques to solve problems and doing it consistently without mistakes. Just practicing a lot of questions is not enough. You need to focus on each sub-skill and questions must be designed for that. A good sequence of graded questions can help you build your problem skills.Multi-Concept Applications: Once you are clear about your concepts and skills, you need to learn how to apply it to a complex problem. You must learn how to analyze the question, do a route-map and figure what concepts are relevant and what are not. Many questions may involve multiple concepts. This again requires focussed training.Revision and Practice: After the first three steps (along with it), you must keep practicing and this is where tests and practice questions help. Unlike other steps this must NOT be graded or sequenced. It must be in random order, so that your brain is trained to jump from topic to topic. Also it forces a discipline of starting from each question and quickly thinking of how to solve it. As you near the exam, you need this more and more.Exam Strategies: Things like how to manage your time, how to ‘avoid’ or catch silly mistakes, should you guess or not in an MCQ question, what topics to drop, how to eliminate answers, etc. All these are useful but if you focus on it too early, you will miss learning the subject! Many coaching classes and students do that and lose out. Focus on these strategies only towards the end of your preparation. First focus on the first three steps.The first three needs a good teacher.I teach Physics and Mathematics at AhaGuru. We have Live Online Classes that you could join. Every class I give a worksheet and teach the concepts and then ask students to work out problems, give them hints and then finally I solve each question on the board and then answer the doubts the students ask. I have students from all across the country (Delhi, Hyderabad, Vizag, Bangalore, Pune, Cochin, Coimbatore, etc) who attend these classes and they say that it feels much better than direct classes and also save a lot of travel time. You can get more details about our AhaGuru Live Online Classes from our website: www.ahaguru.com or by calling our office: +91 9600100090. You can also ask them for a free sample video lesson that you can try out before joining.All the best for your IIT JEE preparations!Balaji Sampath