183
votes
Accepted
Google Sheets formula for "if contains"
You can use REGEXMATCH:
=IF(REGEXMATCH(A1, "sites"), 1, 0)
To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.
- 4,761
56
votes
Accepted
Stop a '+' from generating a formula
The easiest workaround is to enter an apostrophe ' as the first character, right before the +.
Another approach is to enter the contents as a string formula like ="+5 blah".
An initial plus sign is ...
- 676
40
votes
Accepted
Concatenating only filled cells
Try TEXTJOIN:
=textjoin(" vs ",1,C10:C14)
The second parameter is set to 1 indicating that empty cells should be ignored. Zero will include those empty cells.
- 17.4k
32
votes
How to Fill a Column with Sequential Dates in Google Sheets
As it was already pointed out in other answers, you have to select at least two cells containing successive dates.
However, this didn't work for me at first because my Google Sheet had the wrong ...
30
votes
Google Sheets formula for "if contains"
If you, as Ryan Shillington suggests, want to know the number of cells within a range that has "sites" in it, you might try:
=COUNTIF(A1:A100;"*sites*")
The * acts as a wildcard notion, so it will ...
- 301
21
votes
Google Sheets formula for "if contains"
This returns a #VALUE! error if the search term isn't found:
=IF(SEARCH("sites", B2) > 0, 1, 0)
This uses the same logic but catches the error and returns a 1/0 for success/failure:
=IF(...
- 1,844
19
votes
Accepted
How to add a note or comment into a formula in Google Sheets?
You can have the same functionality in sheets as well.
&T(N("comment"))
OR
=IFERROR(A1/A2,"") &T(N("comment"))
OR
=YourFormula+N("comment")
(This last one works when the formula gives ...
- 12.6k
18
votes
Accepted
How can I create a unique list of data across multiple rows AND columns?
=UNIQUE({A:A;B:B;C:C})
=ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;B:B;C:C}),
"select Col1 where Col1 is not null")))
- 26.2k
17
votes
Accepted
Change date format [YYYYMMDD] to [MM/DD/YYYY]
Assuming your string is in cell A1, this formula will convert it to a date. You can then format the date however you prefer.
=date(left(A1,4),mid(A1,5,2),right(A1,2))
Or, take the leftmost four ...
- 52.3k
17
votes
Accepted
HAVING clause in Google Sheets QUERY
Based on this Google Forum answer, I've nested the query:
=QUERY(
QUERY(
final!H2:H,
"SELECT H, COUNT(H) GROUP BY H ORDER BY COUNT(H) DESC"
),
"WHERE Col2 > ...
- 1,793
13
votes
Is there an option to add a time picker on Google Sheets?
Go to Data > Data Validation
Select "List of Items"
Paste this in the box:
01:00A, 01:15A, 01:30A, 01:45A, 02:00A, 02:15A, 02:30A, 02:45A, 03:00A, 03:15A, 03:30A, 03:45A, 04:00A, 04:15A, 04:30A, 04:...
- 131
13
votes
How can I create a unique list of data across multiple rows AND columns?
=unique(flatten(A1:Z))
I think this will work and it worked for me.
- 131
12
votes
Return a blank when cell reference is blank
Google Sheets as well as other spreadsheet application handles dates as serialized number. The large number that you refer that is displayed when a cell on AL column is blank is very likely that it is ...
- 44.6k
12
votes
How to show the current day of the week in Google Sheets
solution without formatting:
=Text(TODAY(),"dddd")
see https://support.google.com/docs/answer/3094139
- 121
11
votes
How to Fill a Column with Sequential Dates in Google Sheets
The next level would be to use ARRAYFORMULA:
=ARRAYFORMULA(TEXT(ROW(INDIRECT("A"&DATEVALUE("2018-4-13")&
":A"&DATEVALUE("2018-4-30"))), "dd/mm/yyyy"))
- 26.2k
11
votes
Can I use column headers in a =QUERY?
Is it at all possible to use column headers like this?
Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ...
- 119
11
votes
Is there any way to use a formula in a Google Sheets filter view?
Remember to us the cell under the filter heading in the formula, so if your filter is on A3, use A4.
Here is an example for a filter in cell A1, checking if it matches a value in C1
=(A2=$C$1)
for ...
- 191
11
votes
Google Sheets formula for "if contains"
A solution without REGEX:
=if(iserror(FIND("sites",B2)),0,1)
capitalization matters and if logic must be reversed, given that answer is true when the string is not found.
- 211
11
votes
Compare two lists and display missing items in a Google Spreadsheet
Formula
=ArrayFormula(FILTER(A4:C7,ISERROR(match(A4:A7,D4:D5,0))))
Explanation
MATCH returns an error if the invited doesn't appear on the attended list.
ISERROR converts errors to TRUE and values ...
- 44.6k
10
votes
How do I output a string containing quotation marks?
Here is another way to do it in Google Sheets:
If A1 = Hello" World
Then you can compare to it using =IF(A1 = CONCATENATE("Hello",char(34)," World"),TRUE,FALSE)
That way you are using char(34) to ...
- 109
10
votes
Accepted
Combine Custom Formula with Colour Scale in Conditional Formatting
This is now possible by inserting a formula instead of a static number into the box for min/mid/max amount.
8
votes
Can I use column headers in a =QUERY?
I was searching for a suitable answer to this question, also, since using column letters like A, B, C is not good for flexibility and becomes a maintenance hassle. Every time your source data columns ...
- 445
8
votes
Stop a '+' from generating a formula
Do a Find & Replace for = with ' and check "Also search within formulas."
- 91
Only top scored, non community-wiki answers of a minimum length are eligible