Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 26 Aug 2014 16:11:08 +0200COUNTING TEXT IN COLUMNShttps://ask.libreoffice.org/en/question/16253/counting-text-in-columns/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 pleaseWed, 17 Apr 2013 03:58:57 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/Comment by qubit for <p>I have columns with a mixture of text and numbers eg</p>
<p>CM71846
CF21546
CM78946
CF12456
CF24566</p>
<p>How can I count the number of CF and number of CM please</p>
https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=16703#post-id-16703@Nifty -- If one of the answers below works well for you, please mark it as correct.
Thanks!Sat, 27 Apr 2013 20:39:32 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=16703#post-id-16703Comment by Nifty for <p>I have columns with a mixture of text and numbers eg</p>
<p>CM71846
CF21546
CM78946
CF12456
CF24566</p>
<p>How can I count the number of CF and number of CM please</p>
https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=16963#post-id-16963Thanks great help by allThu, 02 May 2013 11:31:13 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=16963#post-id-16963Answer by m.a.riosv for <p>I have columns with a mixture of text and numbers eg</p>
<p>CM71846
CF21546
CM78946
CF12456
CF24566</p>
<p>How can I count the number of CF and number of CM please</p>
https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?answer=16273#post-id-16273There is a function to do it:
=COUNTIF(A1:A10;"CF.*")
Please take a look in the help about the function and the regular expressions.Wed, 17 Apr 2013 10:35:49 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?answer=16273#post-id-16273Comment by Nifty for <p>There is a function to do it: </p>
<p>=COUNTIF(A1:A10;"CF.*")</p>
<p>Please take a look in the help about the function and the regular expressions.</p>
https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=16962#post-id-16962Sorry I tried that and for sum reason function would not work returns 0 all the time.Thu, 02 May 2013 11:30:33 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=16962#post-id-16962Comment by artfulrobot for <p>There is a function to do it: </p>
<p>=COUNTIF(A1:A10;"CF.*")</p>
<p>Please take a look in the help about the function and the regular expressions.</p>
https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=38881#post-id-38881@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.Tue, 26 Aug 2014 16:11:08 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?comment=38881#post-id-38881Answer by razon_22 for <p>I have columns with a mixture of text and numbers eg</p>
<p>CM71846
CF21546
CM78946
CF12456
CF24566</p>
<p>How can I count the number of CF and number of CM please</p>
https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?answer=16254#post-id-16254![image description](/upfiles/1366164813602474.png)
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.Wed, 17 Apr 2013 04:22:38 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?answer=16254#post-id-16254Answer by JohnSUN for <p>I have columns with a mixture of text and numbers eg</p>
<p>CM71846
CF21546
CM78946
CF12456
CF24566</p>
<p>How can I count the number of CF and number of CM please</p>
https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?answer=16281#post-id-16281If you have many text variations, you can use a pivot table
![Quick Counting With Pivot Table](/upfiles/13662008516555801.gif)Wed, 17 Apr 2013 14:14:50 +0200https://ask.libreoffice.org/en/question/16253/counting-text-in-columns/?answer=16281#post-id-16281