Transcripts for the above video clip:

Search

The SEARCH function in Excel allows you to look within a cell and find at which character a certain key character appears. So, for example, if you look at cell A4, you’ll see the # symbols seem to have some sort of meaning – there’s a description, a code  and some sort of branch number or product. Using the search function we can actually look in the cell and say where does the first # occur and where does the second # occur.  So now I activate the function wizard, I find the SEARCH function and you’ll see the options you have got are firstly, find text – in this case I insert “#”, secondly -where must Excel find this – highlight the cell A4 and you can already see it has told me it is at character 14 and then thirdly Excel asks for Start_num (Check below on the window for the meaning of Start_num). For the first example we are going to leave Start_num blank. I will come back to this later.  If you now click OK you will see we get told that at character 14 is the first #. Why is this useful? Now that we know where the first # is, we can now create a cell that will extract only  the information only up to the first #. We can do this by using the LEFT function (which is explained in another video). Open the function wizard and click on the LEFT function, in this window for text I am going to insert  A4 and the num_chars – I want to pull out all the information up to the first #, so click in cell B4 - and the result  =  Copper-Coils # . Perhaps we don’t want the # to come in so add a -1, so num_chars is B4-1. Click OK and you’ll see that in a very quick way we have created a useful function, which I can now copy down and you’ll see the same result in the cells below ie it has found the first # at character 20 and the words before the first # are Variable Resistors. Now we are going to do the same thing to extract the second #. Again use the SEARCH function- Find text- looking for the “#” again, we want it to look in cell A4 again. If you leave it like this you will see that it again finds the first # at character 14. Now we use the syntax Start_num and we will now tell Excel not to start at number 1 – click in cell B4 and again you will see the result is 14 because Excel is starting at 14 and immediately finding a # there.  So in Start_num we need to add a +1 (so enter B4+1) and then you’ll see it tell us the next # is at 26. Press OK. So now you have found out the the first # is at 14 and the second # is at character 26. Using this information we can now extract the last 3 digits from this cell and we are going to use the MID function (which you can learn about in another video). The MID function asks for  “Text” and I will insert A4, where must Excel start – we now know that the second # is at character 26, so I am going to click on it (E4) – for Num_chars I will insert a 3. The result you are getting now  =#10. This indicates that the Start_num shouldn’t be where the # is (at 26) but at the #+1 ie it is E4+1 and now the result is correct at 100. So now I can say OK and copy down. You’ll now see that it quickly allows you to  extract  the numbers that you want.  This function is very similar to the FIND function- it is important to understand the differences.  If you use the FIND function you will find that it is case sensitive and it doesn’t allow you to use wildcard characters. The wildcard characters will be explained later. With SEARCH it is not case sensitive, so, for example, instead of looking for a # we were looking for a  capital C – then we would  have to use FIND. If we wanted to find any c (regardless of case) then we could use SEARCH. The SEARCH key also allows you to do wildcard characters. The wildcard characters allows you to do a search where you are not quite sure what some of the search terms will be. So, for example, if we include a ? in our search, it represents one character. Go to  SEARCH on the function wizard. In the text block I am going to say find “ls?#”. So we are asking Excel to find “ls, something we are not sure of (?), and then #”. For the block “within text” we will insert A4 and then leave Start_num blank. Click OK. You’ll see it pull 11 out- it says at character  11 we have this set of characters. If I copy this down, you’ll see in the cell below it gives an error message because in the cell A5 it can not find the set of characters “ls?#” in this cell. This function therefore allows you to be very specific about what you are looking for. The * allows you to search for a number of characters between two characters. The best way to explain is with an example. Go  to the function wizard and SEARCH again. Now the text I want to find are the two #s – I don’t really know how many characters are going to be in between so I insert  “#*#”  and  within text I insert A5 and click OK. It will say that at character 20 we have a #, with some characters and another #. Now when I copy it up, you’ll see it works as well in the cell above, even though there are more characters in the middle – because what you have asked Excel to find is a #, with some characters in between and another #. If by chance you happen to be searching for an *. If you use * in the way shown above, Excel will assume you are looking for a number of characters, it won’t see it as an asterisk. What you do to tell Excel that you are looking for an actual * you put           

̃ in front which tells Excel to ignore the * for its normal use and rather search for the actual *. And you can do that as well with the ?.