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

Ask Your Question


asked 2013-04-17 03:58:57 +0200

Nifty gravatar image

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 flag offensive 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.


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

Thanks great help by all

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

3 Answers

Sort by » oldest newest most voted

answered 2013-04-17 14:14:50 +0200

JohnSUN gravatar image

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

Quick Counting With Pivot Table

edit flag offensive delete link more

answered 2013-04-17 10:35:49 +0200

m.a.riosv gravatar image

There is a function to do it:


Please take a look in the help about the function and the regular expressions.

edit flag offensive delete link more


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

Nifty gravatar imageNifty ( 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.

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

answered 2013-04-17 04:22:38 +0200

razon_22 gravatar image

image description

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-04-17 03:58:57 +0200

Seen: 25,574 times

Last updated: Apr 17 '13