CODEWITHSUNDEEP
excel
user1810700
user1810700

Reputation: 9

Searching for text in excel

I want to count the number of occurrences of a specific text string inside a range of cells in excel. Some cells contain only one word but others contain multiple words.

Upvotes: 0

Views: 222

Answers (2)

Jüri Ruut
Jüri Ruut

Reputation: 2530

A1:A3: Cells with text strings
C1: Word searched

We can use an array formula (Ctrl-Shift-Enter from formula window, curled brackets are inserted by Excel itself):

   ={(SUM(LEN(A1:A3))-SUM(LEN(SUBSTITUTE(A1:A3,$C$1,""))))/LEN($C$1)}

The searched word is deleted from the initial set of strings, which shortens by n*length.

Upvotes: 0

user1789769
user1789769

Reputation:

use countif function --------------> =COUNTIF(range,criteria)

you pass the text string as a criteria put it in quotes.

Upvotes: 1

Related Questions