# Tag Info

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,809
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 ...
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.7k

### 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

### 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,872
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.9k
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,803
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.8k
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.7k

### 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

### 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

### 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 ...

### 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.8k
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.

• 121

### 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

### 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

### 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 ...

### 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

### 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 ...
• 455
Accepted

### Insert literal text that begins with = sign

In Google Sheets, as in some other spreadsheet apps, typing a single apostrophe at the beginning of your entry will prevent the software parsing your input. Type '=Hello and =Hello will appear in the ...
• 206

### Combine two columns into a list of all possible combinations of entries

Update March 2024 Google Sheets nowadays has tocol() and torow() functions that lets you avoid the 50,000 character limitation that bugs solutions that use join(). Use this pattern: =arrayformula(...
• 15.2k

### Stop a '+' from generating a formula

Do a Find & Replace for = with ' and check "Also search within formulas."
• 89

### Stop a '+' from generating a formula

In your Google Sheets select the area and go to FORMAT -----> NUMBER -----> PLAIN TEXT. Now the formulas will not work anymore!