Using array formulas for text search in Excel

Most of the people are not aware of array formulas in Microsoft Excel. These formulas are primary used to linear algebra matrix operation. Matrix multiplication is a typical example of this type of operation. These mathematical operations are not common in business and mathematicians prefer specialized software like Matlab or Octave to perform their calculations.

However, array formulas can be powerful helper in many other operation. We will show how to use array formula for text search. They could save you from writing VBA macros. I will demonstrate usability of array formulas on a text search example. Finding a substring in a string is a trivial task. But what if we want to search a string for multiple strings? It is much more complex task. Let’s find some practical example.

We are managers of a travel agency and we have created a survey. There was one question in the survey: Please describe your ideal holiday. Now our agency has free places for a trip to Paris in May. We would like to offer these places to the specific potential customers based on the “ideal holiday” survey question. Unfortunately, that was the open question so people were filling in answers as text in sentences. And we don’t have time to read all the answers. It would be useful to select answers which contains words like Paris, France, Eiffel Tower, spring, May etc. It seems a little complicated but it can be done by one formula. You will see that the well-known functions like SEARCH, ISNUMBER or IF can work with multiple input cells as well as with a one input cell.

Let’s consider another example. We have a log data from firewall and we have some specific port numbers which we want to pay attention to. We want to check our log data and list the port numbers present in each row. It is obvious that there are many better tools for log analysis however Microsoft Excel can do this extremely fast it is available almost everywhere.

Look at the following data. I have downloaded some sample data here http://www.monitorware.com/en/logsamples/cisco-pix-61(2).php.

a001-001

We have data in the A column and list of the key ports in B column. There is the output which we want to get in C column. This is the formula which will start with:

=CONCAT(IF(ISNUMBER(SEARCH($B$2:$B$5,A2))=TRUE,$B$2:$B$5&", ",""))

We will insert this formula into cell C2. Make sure you press Ctrl+Shift+Enter to confirm the entered formula. If you press Enter only this formula won’t be considered as array formula and you will get incorrect result!

Now we will look closely on the formula. A SEARCH function searches the string in A2 cell for all substrings in range $B$2:$B$5. Then we use a ISNUMBER function to check if the SEARCH returns a number. The SEARCH function will return a number only if it finds the specified substring. With IF function we skip all port numbers which haven’t been found in the cell A2. And with CONCAT function we will be able to display all the results in one cell.

Finally, we want to have little more fancy result. We don’t want to have a comma after last port number. We can doit with a combination of LEFT and LEN function. This is our final formula:

=LEFT(CONCAT(IF(ISNUMBER(SEARCH($B$2:$B$6,A2))=TRUE, $B$2:$B$6&",  ", "")), MAX(LEN(CONCAT(IF(ISNUMBER(SEARCH($B$2:$B$6, A2))=TRUE,$B$2:$B$6&", ", "")))-2,0))

Again don’t forget to press Ctrl+Shift+Enter after inserting the formula. Then you can expand the formula to the rest of the rows. If you have a newer version of Microsoft Excel you can double click on the right down corner of the C2 cell.

a001-002

 

Leave a Reply

Name and email address are required. Your email address will not be published.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

You may use these HTML tags and attributes:

<a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <pre> <q cite=""> <s> <strike> <strong> 

%d bloggers like this: