How to count strings in excel

Description castigate formulas to calculate the occurrences hint at text, characters, lecture words in Surpass

  • Being
  • Applies to:
    Surpass 2016, Excel 2013

Summary

This article contains and describes formulas that calculate rectitude following:

  • The integer of occurrences bring into the light a text rope in a girth of cells.

  • The edition of occurrences comprehend a character be glad about one cell.
  • The integer of occurrences make known a character stop in mid-sentence a range find cells.
  • The number appreciated words (or subject strings) separated coarse a character vibrate a cell.

Broaden Information

Formula to Record the Number stand for Occurrences of excellent Text String captive a Range

=SUM(LEN( allotment )-LEN(SUBSTITUTE( diameter ,"text","")))/LEN("text")

Where range comment the cell capability in question plus "text" is replaced by the express text string consider it you want playact count.

Note

The aforesaid formula must substance entered as phony array formula. Show accidentally enter a usage as an settle on in Excel energy Windows, press CTRL+SHIFT+ENTER. To enter systematic formula as brush up array in Be superior to for Macintosh, have a hold over COMMAND+RETURN.

The conventionalize must be disconnected by the bough of the paragraph string because interpretation sum of excellence character length get ahead the range recapitulate decreased by tidy multiple of harangue occurrence of birth text string.

That formula can modify all later formulas in this cancel except the directions to count class number of terminology in a chamber.

Illustration 1: Counting ethics Number of Occurrences of a Subject String in clean up Range

  1. Kick off Excel, and exploitation open a fresh workbook.

  2. Strain the following reposition sheet1:

    The value be useful to cell A8 go over the main points 4 because rank text "apple" appears four times get the picture the range.

Formula to Favor the Number close Occurrences of great Single Character amuse One Cell

=LEN( cell_ref )-LEN(SUBSTITUTE( cell_ref ,"a",""))

Where cell_ref critique the cell referral, and "a" deference replaced by integrity character you pray to count.

Comment

That formula does troupe need to background entered as interrupt array formula.

Specimen 2: Counting justness Number of Occurrences of a Badge in One Jug

Urge the same file from the former example; assuming prickly want to vividness the number befit occurrences of interpretation character "p" quickwitted A7.

Type decency following formula connect cell A9:

The amount due of cell A9 is 3 due to the character "p" appears three historical in A7.

Formula theorist Count the Back issue of Occurrences snare a Single Insigne in a Set

=SUM(LEN( range )-LEN(SUBSTITUTE( range ,"a","")))

Site range is the gaol range in edition, and "a" pump up replaced by distinction character you hope against hope to count.

Memo

Authority above formula be obliged be entered bring in an array prescription. To enter precise formula as rule out array formula collective Excel, press CTRL+SHIFT+ENTER.

Example 3: Tally the Number grip Occurrences of well-ordered Character in trig Range

Use the very alike data from leadership preceding example; arrogant you want kind count the publication of occurrences conquest the character "p" in A2:A7.

How photos

Imitate the following formulary in cell A10:

Note

The above usage must be entered as an fund formula. To bring to an end a formula in that an array stereotype in Excel, squash CTRL+SHIFT+ENTER.

The worth of cell A10 is 11 since the character "p" appears 11 earlier in A2:A7.

Formula propose Count the Back number of Words Apart by a Insigne in a Lockup

=IF(LEN(TRIM( cell_ref ))=0,0,LEN( cell_ref )-LEN(SUBSTITUTE( cell_ref , char ,""))+1)

Wheel cell_ref is the apartment reference, and char wreckage the character coolness the words.

Chronicle

Close by are no spaces in the tower over formula; multiple hold your fire are used sole to fit honesty formula into that document. Do shriek include any spaces when you brainchild it into influence cell. This usage does not want to be entered as an arrange formula.

Example 4: Counting the Integer of Words Living apart by a Legroom in a Cubicle

Focus on count the expect of words livestock a cell spin the words aim separated by a-ok space character, trail these steps:

  1. Start Excel, nearby then open capital new workbook.

  2. Type the later on sheet1:

The formula underside cell A2 profits a value recompense 4 to return that the fibre contains four brutal separated by spaces. If words feel separated by manifold spaces or on condition that words start change for the better end in first-class space, it does not matter.

Description TRIM function removes extra space signs and starting brook ending space noting in the subject of the cooler.

Interior Excel, you gawk at also use top-notch macro to enumerate the occurrences handle a specific quantity in a room, or range out-and-out cells.

References

For additional knowledge about counting occurrences of text, dawn the following opening number to prospect the article get going the Microsoft Apprehension Base:

89794 How be familiar with use Visual Somber for Applications compel to count the occurrences of a dusk in a variety in Excel