In this article, we will learn how to create an array of strings and check how many times each string appears in a range of cells in Excel. This can be useful for tasks such as data analysis, text mining, or pattern recognition.
An array is a collection of values that are stored in a single variable. In Excel, an array can be either a constant array, which is defined by entering values in curly braces, or a dynamic array, which is created by using a formula that returns multiple results.
A string is a sequence of characters, such as a word, a sentence, or a code. In Excel, a string can be entered in a cell, a formula, or a VBA macro.
To check the occurrence of a string in a cell, we can use the FIND or SEARCH function, which returns the position of the string within the cell, or the ISNUMBER function, which returns TRUE if the string is found and FALSE otherwise.
To check the occurrence of an array of strings in a range of cells, we can use an array formula, which is a formula that performs calculations on multiple values at once. An array formula must be entered by pressing Ctrl + Shift + Enter, and it will display curly braces around it in the formula bar.
Procedure
To create an array of strings and check their occurrence in cells in Excel, we can follow these steps:
- Define the array of strings that we want to search for. We can either enter the values in curly braces, such as {“LLC”, “llc”, “limited”, “Ltd.”}, or use a function that returns an array, such as TEXTSPLIT (text, delimiter), which splits a text into an array based on a delimiter.
- Define the range of cells that we want to search in. We can either enter the cell references, such as A2:A10, or use a function that returns a range, such as FILTER (range, condition), which filters a range based on a condition.
- Enter an array formula that counts the number of occurrences of each string in the array within the range of cells. We can use a combination of functions, such as SUM, IF, ISERROR, FIND, and LEN, to perform this calculation. For example, the following array formula counts the number of occurrences of the array of strings in G1:G4 within the range of cells in A2:A10:
=SUM (IF (ISERROR (FIND (G1:G4,A2:A10)),-1,1))
This formula works by first looking for each string in the array within each cell in the range, and returning an error value if not found, or the position if found. Then, it converts the error values to -1 and the position values to 1, and sums up the resulting array. The sum will be equal to the number of occurrences of the string in the range.
- Copy the array formula to adjacent cells to get the count for each string in the array. Alternatively, we can use a function that returns an array, such as MAKEARRAY (rows, columns, formula), which creates an array with the specified number of rows and columns, and fills each element with the result of a formula. For example, the following array formula creates a 4×1 array with the count of each string in the array in G1:G4 within the range of cells in A2:A10:
=MAKEARRAY (4,1,SUM (IF (ISERROR (FIND (G1:G4,A2:A10)),-1,1)))
Example
Let’s see an example of how to create an array of strings and check their occurrence in cells in Excel. Suppose we have a list of company names in column A, and we want to count how many of them contain the words “LLC”, “llc”, “limited”, or “Ltd.” in any combination. We can use the following steps:
- Define the array of strings that we want to search for. We can enter the values in curly braces in cell G1, such as {“LLC”, “llc”, “limited”, “Ltd.”}.
- Define the range of cells that we want to search in. We can enter the cell references in cell H1, such as A2:A10.
- Enter an array formula that counts the number of occurrences of each string in the array within the range of cells. We can use the following formula in cell I1:
=SUM (IF (ISERROR (FIND (G1:G4,H1)),-1,1))
Remember to press Ctrl + Shift + Enter to enter the array formula correctly. The result in cell I1 is 2, which means that two company names in the range contain the word “LLC” or “llc”. 4. Copy the array formula to cells I2, I3, and I4 to get the count for each string in the array. The results are 2, 3, and 1, respectively, which means that two company names contain the word “limited”, three company names contain the word “Ltd.”, and one company name contains both words.
The following table shows the final result:
| Company Name | LLC | llc | limited | Ltd. |
|---|---|---|---|---|
| ABC LLC | 1 | 0 | 0 | 0 |
| XYZ llc | 0 | 1 | 0 | 0 |
| PQR Limited | 0 | 0 | 1 | 0 |
| LMN Ltd. | 0 | 0 | 0 | 1 |
| RST ltd. | 0 | 0 | 0 | 1 |
| UVW Limited | 0 | 0 | 1 | 0 |
| EFG Ltd. | 0 | 0 | 0 | 1 |
| HIJ Limited Ltd. | 0 | 0 | 1 | 1 |
| Total | 2 | 2 | 3 | 4 |
Other approaches
There are other ways to create an array of strings and check their occurrence in cells in Excel, such as:
- Using the COUNTIF or COUNTIFS function, which counts the number of cells that meet one or more criteria. For example, the following formula counts the number of cells in A2:A10 that contain the word “LLC” or “llc”:
=COUNTIF (A2:A10,”LLC“)+COUNTIF (A2:A10,”llc“)
- Using the FILTERXML function, which returns an array of values from an XML document based on an XPath expression. For example, the following formula splits the text in cell A2 into an array of words based on the space character, and then counts the number of words that match the array of strings in G1:G4:
=SUM (IF (ISERROR (MATCH (FILTERXML (“<t><s>”&SUBSTITUTE (A2,” “,”</s><s>”)&”</s></t>”,”//s”),G1:G4,0)),-1,1))
- Using the Text-to-Columns feature, which splits a text into multiple columns based on a delimiter or a fixed width. For example, we can select the range A2:A10, go to the Data tab, click Text to Columns, choose Delimited, select Space as the delimiter, and click Finish. This will split the company names into separate columns, and then we can use the COUNTIF or COUNTIFS function to count the occurrence of each string in the array.
