How to search for a character in Excel using the SEARCH function. Includes how to use the wildcard characters
Search for character in cell
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 hash 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 hash occur and where does the second hash 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 “hash”, 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 hash. Why is this usefulquestion mark Now that we know where the first hash is, we can now create a cell that will extract only the information only up to the first hash. 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 hash, so click in cell B4 – and the result = Copper-Coils hash . Perhaps we don’t want the hash 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 hash at character 20 and the words before the first hash are Variable Resistors. Now we are going to do the same thing to extract the second hash. Again use the SEARCH function- Find text- looking for the “hash” 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 hash 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 hash 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 hash is at 26. Press OK. So now you have found out the the first hash is at 14 and the second hash 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 hash 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 =hash10. This indicates that the Start_num shouldn’t be where the hash is (at 26) but at the hash+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 hash 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 question mark in our search, it represents one character. Go to SEARCH on the function wizard. In the text block I am going to say find “lsquestion markhash”. So we are asking Excel to find “ls, something we are not sure of (question mark), and then hash”. 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 “lsquestion markhash” in this cell. This function therefore allows you to be very specific about what you are looking for. The star 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 hashs – I don’t really know how many characters are going to be in between so I insert “hashstarhash” and within text I insert A5 and click OK. It will say that at character 20 we have a hash, with some characters and another hash. 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 hash, with some characters in between and another hash. If by chance you happen to be searching for an star. If you use star 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 star you put
Tilde in front which tells Excel to ignore the star for its normal use and rather search for the actual star. And you can do that as well with the question mark.