Do you use the Microsoft Excel SUBSTITUTE function very often? It’s a handy way to count items in a cell, when they’re separated by commas or spaces. The examples below show different ways to use this function – have you tried the variation in the last example?
Count Words in a Cell
In the first example, we’ll count the items in a cell, when they are separated by commas. The items are in cell A2, and this formula is entered in cell B2:
=LEN(A2)-LEN(SUBSTITUTE(A2,”,”,””))+1
This formula uses the Excel LEN function and SUBSTITUTE, a text function, to count the items.
- LEN(A2) – Counts the number of characters in cell A2
- SUBSTITUTE(A2,”,”,””) – Replace each comma with an empty string
- LEN(SUBSTITUTE(A2,”,”,””) – Count characters in cell A2, with all the commas are removed
- Subtract amount 2 from amount 1, to get the number of commas
- Add 1, to get the number of items
SUBSTITUTE Tips
- SUBSTITUTE function can replace ALL instances of specific text
- To replace only one occurrence, use the instance_num argument
- Replacements are case sensitive
- For non-case sensitive replacements, you can use the REPLACE function
TRIM the Spaces
If you’re using space characters to separate the items in a cell, be sure to use the TRIM function, to clean up any leading, trailing, or extra spaces.
Without TRIM, the item count could be incorrect. Here is an example without TRIM:
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))+1
The counts in rows 4 and 5 are too high, because of extra spaces.
Here’s the revised formula to count items in a cell with space character separators:
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2),” “,””))+1
The counts with this formula, in column B, are correct, and the incorrect counts (with the old formula), are in column C, for comparison
Check for Minimum Count
Instead of just counting the items in the cell, you can change the end of the formula, and check for a minimum item count.
In this example, a street name is entered in column A.
- There should be at least 3 items entered in the cell, such “123 Main Street”.
- If there are 3 or more items, there will be 2 or more spaces.
Change the end of the formula – instead of adding 1, to get the word count, check that the number of spaces is 2 or more
=LEN(A2)-LEN(SUBSTITUTE(A2,”,”,””))>=2
The results are TRUE or FALSE, and you could use a formula like this as a custom rule for data validation in a cell.
Check Minimum Count Version 2
Another way to check for a minimum word count is with this shorter formula – thanks to UniMord for suggesting it.
Instead of checking the cell contents with LEN, the formula replaces the 2nd space character (if there is one), and then compares with the original value.
=TRIM(A2)<>SUBSTITUTE(TRIM(A2),” “,””,2)
How It Works
This formula uses 2 as the 3rd argument in SUBSTITUTE, to specify which space character (instance number) to replace.
The second space is removed, and you can see the results of that step in column C(it’s there as a demo only).
Usually, we omit the 3rd argument, so ALL instances of the text are replaced. But in this case, it’s helpful to just replace the 2nd space character, as our test.
- We want a minimum of 3 items, so there should be at least 2 space characters.
- If we replace the 2nd space, the result is different from the original value
- So, if the original is not equal to the revised value, there are 3 or more items.
Download the Sample File
To download the Count Items in a Cell sample file, go to the Excel Files page on my Contextures website. In the Functions section, look for FN0053 – Check Item Count with SUBSTITUTE
There are more SUBSTITUTE examples on my Contextures site, with a workbook that you can download, to try the SUBSTITUTE formulas from the tutorial on that page.
______________