{"id":4659,"date":"2016-10-25T11:26:53","date_gmt":"2016-10-25T09:26:53","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4659"},"modified":"2023-11-30T21:13:49","modified_gmt":"2023-11-30T20:13:49","slug":"count-text-occurrences-excels-substitute-formula","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/count-text-occurrences-excels-substitute-formula\/","title":{"rendered":"Count Text Occurrences with Excel’s SUBSTITUTE Formula"},"content":{"rendered":"

What does it do?<\/strong><\/em><\/p>\n

It\u00a0replaces specific text in a text string i.e. Substitutes new text for old text in a text string.<\/p>\n

Formula breakdown:<\/strong><\/em><\/p>\n

=SUBSTITUTE(text<\/span>, old_text<\/span>, new_text<\/span>, [instance num]<\/span>)<\/p>\n

What it means:<\/strong><\/em><\/p>\n

=SUBSTITUTE(This cell<\/span>, From\u00a0this TEXT\u00a0character<\/span>, To this TEXT\u00a0character<\/span>, [In the 1st, 2nd\u2026instance it occurs]<\/span>)<\/p>\n

\n

I had a scenario where I had to count the number of occurrences of a specific character in a cell.<\/p>\n

For example, I had to check the number of asterisks * in a cell. \u00a0Why you ask?<\/p>\n

Well it was because the asterisks corresponded to the number of times the employee was away that year. \u00a0Who would’ve thought of putting asterisks, but there you go!<\/p>\n

At first, I had no clue on how to do this and it was very cumbersome to do this manually!<\/p>\n

Turns out, the SUBSTITUTE\u00a0<\/b>formula in Excel can be used creatively to extract the count easily!<\/p>\n

Here’s an example:<\/p>\n

\"Count<\/a><\/p>\n

We want to return the count of asterisks here, which is 7. We will use a combination of the\u00a0LEN\u00a0<\/strong>formula and the\u00a0 SUBSTITUTE<\/strong> formula.<\/p>\n

We will have 3 main steps:<\/p>\n