Tool that I use: Google Sheets (I do not use Google Apps Script in this case.)
Objective for this question: I would like to exclude basic English words from a list of words by using Google Sheets function. Purpose of this questions is that I would like to do text mining with google sheets.
Current situations:
There is a column that has some english words. This table is gained from these sentences below.
- I have a dog named Ocean.
- I have a book titled Rosetta.
- I do this.
- I do that.
Sheet name: Sheet1
| A | B | |
|---|---|---|
| 1 | Words. | Word count |
| 2 | i | 4 |
| 3 | have | 2 |
| 4 | do | 2 |
| 5 | a | 2 |
| 6 | dog | 1 |
| 7 | book | 1 |
| 8 | named | 1 |
| 9 | ocean | 1 |
| 10 | titled | 1 |
| 11 | rosetta | 1 |
| 12 | . | 4 |
| 13 | that | 1 |
| 14 | this | 1 |
From such a table, I would like to filter some basic English words such as i, have, do, a, and "." .
If it is about filtering small number of values, I know this solution. I can use filter () function to filter i, have, do, a, and "." .
= filter (A2:12, A2:12<>"i",A2:A12<>"have", A2:A12 <> "do", A2:A12<>"a", A2:A12 <>".")
However, if it is about to filter more values, this filter function becomes longer. That will lack readability.
= filter (A2:12, A2:12<>"i",A2:A12<>"have", A2:A12 <> "do", A2:A12<>"a", ..., A2:A12 <>".")
In order to prevent it, I prepared the following list that has basic English words.
Sheet Name: Sheet2
| A | |
|---|---|
| 1 | i |
| 2 | you |
| 3 | he |
| 4 | she |
| 5 | we |
| 6 | they |
| 7 | but |
| ... | ... |
| 1000 | . |
With this above list, I tried to filter the values from Sheet!A1:A but it did not work.
Perform this function at C column of Sheet1.
=filter(A2:A, A2:A<>Sheet2!A2:A)
As a result of function, this created nothing. This function did not exclude values that specified at Sheet2.
Questions: In order to realize this feature with some combination of google sheet functions, how should I do?
