Excel Tip: Count the Number of Occurrences of a Single Character in One Cell

I am currently helping a partner with a conversion from QuickBooks to Dynamics GP--specifically to migrate QuickBooks transaction Class data into GP Analytical Accounting transactions. For various reasons, standard, and even non-standard migration tools have been unable to convert the QuickBooks data, so we've been forced to export the data and import it into GP.

The eConnect integration I developed works great, but one of the annoyances has been preparing a QuickBooks export file for import to GP. If you are familiar with QuickBooks exports, you know that when data is exported to Excel, it typically looks quite presentable, but is not exactly import-friendly.

While trying to map the QuickBooks chart of accounts to the GP chart of accounts, I had to determine how many "levels" the QuickBooks account had. So for instance, there might be Account: Sub-Account: Department: Location: Expense: Sub-Expense. Out of the pile of QB accounts, I needed to determine the maximum number of "levels" the accounts had. Since there is a colon between each level, I just needed to count the number of colons in the cell, and then add one. Simple!

But I am pretty sure I've never had to count the number of occurrences of a value in a single cell. I've used the SEARCH function to determine whether a value exists at all, but never had to count the occurrences of that value.

With Google to the rescue, I stumbled across a Microsoft Support article that provided several examples of how to count the occurrences of a string in an Excel file.

http://support.microsoft.com/kb/213889

And here is the formula for counting the number of occurrences of a character in a cell:

=LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,"a",""))

Pretty darn smart. Maybe, on a good day, with enough sleep, I could have come up with something that elegant, but I am pretty sure that article saved me alot of time.