Transcripts for the above video clip:

FIND Function

The FIND functions tells you the starting position of characters you are searching for within a cell. In this example we have some information here which we have extracted from our computer system. What we want to do is extract the product name, product code and then the unit size out of it. But as you can see there is no standard length here. So what we need to do is some how find out in each cell where this hash sign is and then extract the applicable characters.

We can use the FIND function to find at what position this hash is located. So if I click in this cell, where we are going to identify the character of the first hash, I activate the function wizard, go to text, and you will see there is FIND here, and say OK. What you will see now is it gives us a bit of information so first it tells us give us the starting position, it does specify that FIND is case sensitive. So it says FIND the text, and in this case we know we want a hash and we put it in inverted commas, within text, we are going to point it here and it asks for the start number and you will see you can leave it out or you can specify to start at number 1 so I will say 1. We can say OK. You will see it tells us that this hash is at point 20.

Using this information we can now use the MID function which you can learn about elsewhere on this site, to extract the first part of this cell. So we use the function wizard, find MID, the text you want to specify is here, the start number we know is from the very beginning, so we will put a one in, the number of characters we now know, the hash is at point 20 so we want that but we do not want to include the hash so we subtract one, we say OK, and there we have the extracted product name.

Now we want to extract the unit size. which is sitting after the second hash. If we use this formula again, it will find the first hash again which is at point 20. In order to find the second hash, we can use the FIND function again. Again the text is a hash, we are looking in the same text, but now we know that we don't need to look in the first 20 characters because that hash is there. So we can use this information. Because we don't want to include the search with that hash, we can add 1 and when we click OK we are told that the second hash occurs at point 32.

With this information we can now extract the last 3 digits from after the hash. So if we go here, we again use the MID function, we know the text is here. The start number, we now know we want to start at, the second hash is at 32, so we want to start at 32 and we want to start plus 1. The number of characters we know is 3. So when we say OK what we have is the extracted unit number.

Now to be a bit more complicated we want to extract the product code which is between the two hashes. As you will see we already know where the first hash is and the second hash. So we do not need to get that information, we can now go straight and use the MID function. The text is the same, in this case however the start number we know must start one after the hash so we will go to the information of where the first hash is and we will plus one, and the number of characters will be where the second hash is minus where teh first hash is and because we do not want to include the second hash we need to minus one, and when we say OK, you will see it has extracted the details between the hashes.

Now that everything is set up it should simply be a case of highlighting all these cells, copying them down, you should see that all the product names have been extracted correctly , the unit sizes are correct, and the product codes are correct.