We will be migrating from Ask to Discourse on the first week of August, read the details here

# COUNTING TEXT IN COLUMNS [closed]

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

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-30 17:38:28.248604

@Nifty -- If one of the answers below works well for you, please mark it as correct.

Thanks!

( 2013-04-27 20:39:32 +0200 )edit

Thanks great help by all

( 2013-05-02 11:31:13 +0200 )edit

Sort by » oldest newest most voted

If you have many text variations, you can use a pivot table

more

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.

more

Sorry I tried that and for sum reason function would not work returns 0 all the time.

( 2013-05-02 11:30:33 +0200 )edit

@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.

( 2014-08-26 16:11:08 +0200 )edit

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.

more