Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

Is there a LIKE function in Excel

I have three thousand rows. In one column there is a field that in some instances contains the word "mattress". I want to see how many instances there are.
0
CMChalcraft
Asked:
CMChalcraft
1 Solution
 
Patrick MatthewsCommented:
Use COUNTIF.

For example:

=COUNTIF(A1:A100,"mattress") to see how many cells have ONLY the word "mattress" as their values

=COUNTIF(A1:A100,"*mattress*") to see how many cells have the word "mattress" within their values, as well as potentially some other text
0
 
Phillip BurtonCommented:
find or search can find a value inside a word, e.g.

=if(iserror(SEARCH("mattress",A1)),"No","Yes")

If you want to count multiple instances, then you can use the array formula

=SUM(IF(ISERROR(SEARCH("mattress",A:A)),0,1))

and press Ctrl+Shift+Enter instead of pressing Enter (change A:A to your range).

Alternatively, do a filter on that column, and the number of instances will be in the status bar at the bottom.
0
 
CMChalcraftAuthor Commented:
Thank you. I have used =if(iserror(SEARCH("mattress",A1)),"No","Yes").

This is a spreadsheet a colleague has created and he wants to enter 1 or 0 for each matched instance. I have amended the formula to do this.

Very prompt!

Regards

Chris
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
COUNTIF does the exact same thing, without having to resort to an array formula.  Did you even try it?
0
 
Glenn RayExcel VBA DeveloperCommented:
^ Best solution (COUNTIF).  No helper column needed; inserts the total in one cell; ultimately faster than having several thousand individual formulas returning 1 or 0.
0
 
Martin LissRetired ProgrammerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now