Count Items in a Cell with SUBSTITUTE

Count Items in a Cell with SUBSTITUTE

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.

  1. LEN(A2) – Counts the number of characters in cell A2
  2. SUBSTITUTE(A2,”,”,””) – Replace each comma with an empty string
  3. LEN(SUBSTITUTE(A2,”,”,””) – Count characters in cell A2, with all the commas are removed
  4. Subtract amount 2 from amount 1, to get the number of commas
  5. 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.

substitutecount01

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

substitutecount02

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.

substitutecount04

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)

substitutecount05a

How It Works

This formula uses 2 as the 3rd argument in SUBSTITUTE, to specify which space character (instance number) to replace.

substitutecount06

The second space is removed, and you can see the results of that step in column C(it’s there as a demo only).

substitutecount05

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.

______________

Count Items in a Cell with SUBSTITUTE http://blog.contextures.com/

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.