≡ Menu

How To Automatically Count Instances Of A Specific Character In Excel

2 Flares Twitter 1 Facebook 0 Pin It Share 0 LinkedIn 0 Buffer 1 Email -- Filament.io 2 Flares ×
Image of a colorful abacus

Credit: JD / Flickr

This Excel function is the most useful thing I learned all week:

=LEN(A7)-LEN(SUBSTITUTE(A7,”p”,””))
[source]

The variables are A7 (whichever cell/s you want to select) and “p” (whichever character you want to count).

I had a huge CSV file of tweets — dates, tweet text, interactions, etc. — and I wanted to see what happened how interactions behaved based on how many hashtags and @ mentions I used. It would obviously take forever to read through each tweet and manually input the number of #’s and @’s, so I used this formula and customized it like so in two separate columns (one for #’s, one for @’s):

=LEN(A7)-LEN(SUBSTITUTE(A7,”#”,””))

=LEN(A7)-LEN(SUBSTITUTE(A7,”@”,””))

That showed me what I needed to know, and from there, I used a PivotTable to see what number of hashtags and @ mentions might be ideal for future tweets.

Comments on this entry are closed.

2 Flares Twitter 1 Facebook 0 Pin It Share 0 LinkedIn 0 Buffer 1 Email -- Filament.io 2 Flares ×