235 votes

How to paste a comma separated string over multiple cells in Google Sheets?

From https://support.google.com/docs/answer/6325535?hl=en : Open a spreadsheet in Google Sheets. Paste the data you want to split into columns. In the bottom right corner of your data, click the ...
user avatar
  • 2,451
61 votes

How to paste a comma separated string over multiple cells in Google Sheets?

You are able to paste CSV data as is into the spreadsheet and go to Data selecting Split text to columns... and even further specifying the delimiter: Also, you can simply use keyboard shortcut combo:...
user avatar
  • 24.6k
31 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 ...
user avatar
17 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")))
user avatar
  • 24.6k
9 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"))
user avatar
  • 24.6k
9 votes
Accepted

Google Sheets: Is there kind of map function?

Alternatives: Use ArrayFormula / MMULT functions Limitations: Some formulas should be "redesigned in order to be used with ArrayFormula / MMULT or other similar built-in functions. Related Q&...
user avatar
  • 41.8k
9 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.
user avatar
  • 91
9 votes
Accepted

How to use custom function with arrayformula?

In order to use a custom function with array formula, it should be able to return to get as input and return as result array of values. The custom function provided as example has no input parameter ...
user avatar
  • 41.8k
7 votes

Can you do a UNION SELECT in a Google Sheet?

It is possible to make a union in Google Spreadsheet very easily nowadays with this formula: ={'Sheet1'!A2:A;'Sheet2'!A2:A;'Sheet3'!A2:A} See more info in Google Docs Help: Using arrays in Google ...
user avatar
7 votes
Accepted

Summing top three values in a column from a Google sheet

Formula =ArrayFormula(SUM(IFERROR(LARGE(A1:A2000,{1,2,3}),0))) Explanation LARGE returns the nth greater element. IFERROR if the first argument returns an error, returns the second argument. {1,2,3}...
user avatar
  • 41.8k
7 votes
Accepted

Convert Text String into Date/Time Value

={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), REGEXEXTRACT(A1, "\d+")), TEXT(INDEX(SPLIT(A1, " "), 1, 4), "hh:mm:ss")} with timezone in account: ={DATE(RIGHT(A1, 4), MONTH(MID(A1, 5, 3)&1), ...
user avatar
  • 24.6k
6 votes

Cumulative Sum without Script

The accepted answers are far too complicated for running tallies. A much easier answer is: =SUM(A$1:A1) Substitute the A for the column you want. Substitute 1 for the row you want. Click the small ...
user avatar
6 votes

Cumulative Sum without Script

While the accepted answer is on spot it has a major disadvantage due to the fact that the last parameter of SUMIF needs to be always a "valid range" so when it comes to some more advanced running ...
user avatar
  • 24.6k
6 votes

Average of last 7 non-empty non-blank cells in Google Sheets

TL;DR =AVERAGE(OFFSET(A:A;COUNT(A:A)-7 + MATCH(TRUE;INDEX(A:A<>0);0)-1;0)) Example Explanation First we count how many numbers are in row A with =COUNT(A:A). Cells containing text won't be ...
user avatar
  • 290
6 votes
Accepted

Rearrange Name Format in Google Sheets using one formula only

Delete everything from B:B (including the header) and try this formula in B1: =ArrayFormula({"Reformatted Name";IF(A2:A="",,TRIM(REGEXREPLACE(REGEXREPLACE(A2:A,"Jr$|Jr.$|Sr$|...
user avatar
  • 4,522
5 votes

How to use AVERAGE inside ARRAYFORMULA in Google Sheets

Aggregation functions like AVERAGE, SUM and other similar that get as parameters array of values and just return a single value are not able to return array of values by using ARRAYFORMULA. One ...
user avatar
  • 41.8k
5 votes
Accepted

Print incremental numbers in Google Sheets

It is as simple as using the following sequence formula =SEQUENCE(A1) Or you even could have something like this: =SEQUENCE(A2,1,A4,A6) You could even use the SEQUENCE function for dates =...
user avatar
  • 11.7k
4 votes
Accepted

How to block automatic row and column insertion in Google Sheets?

Short answer Use ARRAY_CONSTRAIN(array,row_limit,column_limit) Explanation ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit ...
user avatar
  • 41.8k
4 votes

Numeric before query but query results are not a numbers

The fix I used for this issue was to coerce the strings into a number by using ARRAYFORMULA(A1:A+0) in a new column adjacent to the original query (let's say column B). I then used another query on ...
user avatar
4 votes
Accepted

Appending text to each column in Google Sheets

Please try: Find: (.+) Replace with: $1-banner.jpg Search: This sheet Search using regular expressions Replace all.
user avatar
  • 16.7k
4 votes

multiple IF statements with between number ranges alternative

CHOOSE() with MATCH() =ARRAYFORMULA(IFERROR(CHOOSE(MATCH(B2:B, {1, 11, 21, 51, 101, 151, 201, 301, 501, 801, 1001, 1223, 1569}), "01-10", "11-20", "21-50", "51-100", "101-150", "151-200", "...
user avatar
  • 24.6k
4 votes

LEFT JOIN two and more tables in Google Sheets

Recently I create a script to do LeftJoin on tables, my script resolve your case. The script join up to 3 right tables, but can be easyli increased to more. So enjoy. Supose you have all in A1:C5 and ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible