I have columns with a mixture of text and numbers eg
CM71846 CF21546 CM78946 CF12456 CF24566
How can I count the number of CF and number of CM please
If you have many text variations, you can use a pivot table
There is a function to do it:
=COUNTIF(A1:A10;"CF.*")
Please take a look in the help about the function and the regular expressions.
@Nifty regular expressions can be disabled in the (advanced) options/settings screen. Perhaps yours is disabled? CF.* would match anything containing CF, followed by any number of characters.
I'm assuming that you only have one letter/number combination in each cell. Use the image above as a guide. In the example, paste this formula in cell B2:
=IF(LEFT(A2,2)="CF", 1, 0)
and this one in C2:
=IF(LEFT(A2,2)="CM", 1, 0)
Drag the cell handle of each of these cells down to fill the formulas down to the end of your data. Then sum columns B and C.
Asked: 2013-04-17 03:58:57 +0100
Seen: 24,948 times
Last updated: Apr 17 '13
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
@Nifty -- If one of the answers below works well for you, please mark it as correct.
Thanks!
Thanks great help by all