So try as I might, I can't see a way that's not pointlessly convoluted to check how many times a given word appears in a given column. For example, let's say column A is text as follows... 1. | Bacon is my favourite 2. | I really like bacon 3. | Prefers eggs 4. | who loves bacon So I want a simple way to return 3, case insensitive instances of the word bacon within a string. Problem being it's a string within a string, so it seems to me like to do this I have to use SEARCH to look for the term bacon, and then sum/count however many rows return a value, which seems pointlessly convoluted. I can't edit the source data, and I need to do this across thousands of rows, for dozens of words. So am I entirely missing something, or is excel indeed pointlessly convoluted.
IIRC - You don't count the words, you count the spaces, something like this - Code: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1
If the strings are in column A, in column B type the word you want, in column C put the following: =(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1) If will display a count of the word, then do an autosum.
@RedFlames I'm not sure I follow, unless you mean you use SEARCH and then get the location at which the word "bacon" is, and then count how many of the rows that return a value, which to me is pointlessly convoluted. @TaRkA DaHl - that would be serviceable if I had one word, but... Basically the scenario is, let's say I asked thousands of people what they think of breakfast, and have a free text response. So I want to look in those respones how many mentioned the words "bacon", "eggs", "coffee" and so on to get an idea of what type of food they like. I also want to look for "love", "hate", "like", "okay" to get a general view of what their overall sentiment towards the meal in general is. As an added bonus, let's say I have emails as well, so I want to also get some info around where they are based on .uk, .jp, .cn and so on. So when all is said and done be able to say things like, people in japan aren't fussed on breakfast in general, but those that like it most usually like eggs, and so on. Clearly this has nothing to do with breakfast, but the example holds. EDIT: Okay I kinda geddit, but then how do I scale that without loads of legwork? So say in sheet 2, I want to stack up keywords in column A, and then an automagical convoluted formula that works out how many times each of those words are in column X of sheet 1 in column B - is this just unrealistic expectations?
something like? - =COUNTIF(A1:A4,"*bacon*") Caveat - running on no sleep, may have mis-read the question
Urgh. Yeah so that's basically exactly it. I looked at COUNTIF but then didn't realise I can just slap *s around bacon. I'd like to stack certain results too, but beggars can't be choosers at this point.
Try VLOOKUP. If your sentences are all in a column, VLOOKUP can find text strings in that column. https://www.excelforum.com/excel-general/821906-vlookup-search-for-text-within-a-string.html https://thesmartmethod.com/vlookup-using-text-strings/
You can also use COUNTIFS to include multiple criteria (but it's strictly AND not OR) and each criterion can be in a different range if required. Not sure if that helps you but it's an option. If you want to use OR logic with multiple criteria then it's a bit trickier, especially if you have lots of criteria. You can do clever stuff with array formulae - see this link: https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/
This is perfect. I slapped something together earlier that let me come to a few initial interesting conclusions, but could do with tidying things up a bit and this will do nicely. Bit-tech to the rescue!
To be honest, Excel is pretty crap a lot of the time, and it's particularly crap at letterz, but the good thing about it is that there are so many online resources that you can almost always find a way to do what you want, even if it's not necessarily particularly straightforward! And when cell formulae fail, there's always VBA
To be fair excel is a pretty solid spreadsheet, i.e. accounting tool. Most of excel's crap behavior extends from people using it as a database and using functions to build queries.
Aye, that's pretty much what I was getting at If I had a pound for every time I've had to tell someone "EXCEL IS NOT A DATABASE" then I'd be extremely rich indeed!
