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 ...
- 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:...
- 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 ...
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")))
- 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"))
- 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&...
- 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.
- 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 ...
- 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 ...
- 265
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}...
- 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), ...
- 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 ...
- 161
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 ...
- 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 ...
- 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$|...
- 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 ...
- 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
=...
- 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 ...
- 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 ...
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.
- 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",
"...
- 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 ...
- 141
Only top scored, non community-wiki answers of a minimum length are eligible