Excel SEARCH Function: The Best Uses for Excel SEARCH Function

Excel SEARCH Function

Microsoft Excel’s SEARCH function may seem to have somewhat limited usefulness. However, when used in conjunction with the MID or REPLACE functions, it can quickly become invaluable. Before going into how to combine it with other functions, though, I will first explain how it works.

Imagine you have, in cell A1, the name Blake Hasenmiller. In cell B1, you have the function:

=SEARCH(“Hasenmiller”, A1,1)

This will search for the word Hasenmiller in Cell A1, starting with the first character in that cell. The result returned will be 7, since the letter H that begins the word Hasenmiller is the 7th character in that cell.

By itself, this is not particularly useful. However, imagine you had a list of names like this in column A, but what you really wanted was just the last name, which you will put in column B. In that case, you could use the function:

=MID(A1,SEARCH(” “,A1,1)+1,LEN(A1)-SEARCH(” “,A1,1))

This will return the name Hasenmiller. It will return whatever it finds after the first space that it comes across.

Let’s break the above function down. The MID function has three parameters. The first parameter is what cell to look at, the second parameter is what character to start at, and the third parameter is how many characters to take. So the MID function will return part of the text of a cell that is referenced.

The LEN function will simply return the number of characters in the referenced cell.

In this case, we are telling the MID function to reference A1. When it comes to the second parameter, which is the starting character, we use the search function to find the space, which in the case of Blake Hasenmiller, will be the 6th character. We then add 1 because we want to start on the character after space, which is the H in Hasenmiller. For the third parameter, which is the number of characters, we first take the number of characters in cell A1 using the LEN function, which is 17, then subtract the position of the space, which as stated earlier, is the 6th character. Since 17 minus 6 equals 11, this function will return 11 consecutive characters, starting with the 7th character, which gives us Hasenmiller.

This function could be dragged down column B to reference a list of names in column A, thus quickly giving you each person’s last name only.

Now, if you wanted the cell to return the person’s last name, followed by a comma and space, followed by their first name, you could use the function:

=MID(A1,SEARCH(” “,A1,1)+1,LEN(A1)-SEARCH(” “,A1,1))&”, “&MID(A1,1,SEARCH(” “,A1,1)-1)

This is the first function followed by an ampersand (the & symbol) which combines multiple text strings, followed by a comma and space, then another ampersand, then a function to take the first name (everything before the space), and combine them all into one big text string. Thus you will get Hasenmiller, Blake.

One last thing to note, the SEARCH function is not case-sensitive. If you want a case sensitive version of this, you can use the FIND function instead. Also, the SEARCH function allows the use of wildcard characters, whereas the FIND function does not.

How to use the SEARCH function in Excel [Tutorial]

Taggar:

How to use the SEARCH function in Excel

Excel Search, Excel Search Text, Excel SEARCH Function