Search and Count

[sndfx69]

Hi All,

I would like a macro that would search a range of cells for a specific word and also count how many times the word has been used in the range, because the word is entered in the range more than once.

Thank you in advance for your support.

 

[Trebor76]

Hi sndfx69,

Welcome to the MrExcel forum!!

A couple of initial questions:

• What is the range you wish to check
• Is the word part of a string or the only item in the cell
• Does it have to be a macro or simply a worksheet formula

Regards,

Robert

 

[sndfx69]

Hi Robert,


• What is the range you wish to check
The range is cells let's say from A1:B4, I would like to be able to select the range.


• Is the word part of a string or the only item in the cell
No, it's a normal word written in a cell as text.

• Does it have to be a macro or simply a worksheet formula
A formula would be better.


My excel sheet is a weekly work Plan for our staff, they type their tasks and I would like to count the number of tasks they entered in their plan, for example if they say that they would "Visit Customers" I would like to know how many times they will be visiting the customers for this week.

Thank you so much for your reply.

Good Day

 

[Trebor76]

OK, try this (just change the range to suit):

=COUNTIF(A1:B4,"*Visit Customers*")

HTH

Robert

 

[sndfx69]

Thank you so much .... God bless you

It worked

 

[Trebor76]

You're very welcome

 

[zaHawk]

Re: Search for wildcard word in cell

Hello

What if I would like to find a word within a given cell containing multiple words?
For example:
In a suppliers spreadsheet.. I would like to know if BOLT (wildcard style) appears in the cell on the left (within a sentence - "Thick Bolts" , "Thin Bolts" . "Threaded Bolts" , etc)

The only difference is.. I don't want to type BOLT in the formula.. it needs to be typed into a separate cell.. then it must be calculated

After that.. I can make it show a value.. and put it in a filter
Then use the filter to show all lines that contain the word BOLT
This is basically a method to lookup all lines that contain the requested word or characters (then minimize it with filtering)
But should be able to handle both letters and numbers... such as me typing in "BOL 123" or "BOL123" in the query cell

Hopefully that is enough information
Thanks

 

[T. Valko]

Re: Search for wildcard word in cell

Hello

What if I would like to find a word within a given cell containing multiple words?
For example:
In a suppliers spreadsheet.. I would like to know if BOLT (wildcard style) appears in the cell on the left (within a sentence - "Thick Bolts" , "Thin Bolts" . "Threaded Bolts" , etc)

The only difference is.. I don't want to type BOLT in the formula.. it needs to be typed into a separate cell.. then it must be calculated

After that.. I can make it show a value.. and put it in a filter
Then use the filter to show all lines that contain the word BOLT
This is basically a method to lookup all lines that contain the requested word or characters (then minimize it with filtering)
But should be able to handle both letters and numbers... such as me typing in "BOL 123" or "BOL123" in the query cell

Hopefully that is enough information
Thanks

Try this...

A1 = Threaded Bolts

F1 = bolt

This will return a result of 1 or 0.

=COUNTIF(A1,"*"&F1&"*")

 

[zaHawk]

It seems to work... maybe too well
I think it is counting spaces as well.. starting with 1 by default.
So when I punch in any query.. the result is 1 as well.
If I punch in anything I know is not in there.. (like "x") the result of "0" is correct.

Any other tweaking options?
Many Thanks

 

[T. Valko]

It seems to work... maybe too well
I think it is counting spaces as well.. starting with 1 by default.
So when I punch in any query.. the result is 1 as well.
If I punch in anything I know is not in there.. (like "x") the result of "0" is correct.

Any other tweaking options?
Many Thanks

Can you show me some more examples of the text strings and of the search criteria?