64
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 Paste icon.
Click Split text to columns. Your data will split into different columns.
To change the delimiter, in the separator box, click
PS: There is no option ...
28
Assuming you want:
Row | A | B | B's formula
--------------------------------
1 | 1 | 1 | =1 (or =A1)
2 | 2 | 3 | =1+2 (or =A2+B1)
3 | 3 | 6 | =1+2+3 (or =A3+B2)
etc...
Then you can use:=sum($A$1:A1) where A1 is the start of numbers to sum. Put that formula in B1 (or where ever you want) and fill it down, and it will sum all cells up ...
28
Yes:
=ArrayFormula(SUMIF(ROW(A1:A10);"<="&ROW(A1:A10);A1:A10))
The numbers in the green circle are the ones being summed, the ArrayFormula is populating all the others based on the cell it was pasted into, in this example I pasted in A11, B1, and C1 to show its effect.
This spreadsheet that I made a while ago attempts to explain how MMULT can be ...
17
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 locale and the cells had the wrong formatting.
Set the locale for your Google Sheet:
File menu → Spreadsheet settings... → Set the correct locale
Configure the ...
12
The key is to enter at least the first two values to get auto fill to work with a pattern
10
In Google Spreadsheets, I would do it a bit different.
Formula
=COUNTA(SPLIT(A1, " "))
Explained
The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances.
The Excel formula gives the same answer, but a bit more laborious:
=LEN(TRIM(A1))-LEN(...
answered Nov 15 '13 at 19:08
Jacob Jan Tuinstra
18.1k12 gold badges71 silver badges158 bronze badges
5
You can transform the columns by using TRANSPOSE, i.e., assume "First member, Second member, Third member" are A1:A3, you can use function in somewhere,
= TRANSPOSE(A1:A3)
will fill column as below,
First member
Second member
Third member
To repeat the column before and after team members, you can try,
= TRANSPOSE (SPLIT(REPT(CONCAT(A1, ","), ...
5
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 counted. Our example would give us 9
From this number we subtract 7 since you want the average of the last 7 values: =COUNT(A:A)-7. This will give us a 2.
Now ...
4
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 parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.
To calculate the number of rows/columns ...
4
=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")))
3
Please try:
Find: (.+)
Replace with: $1-banner.jpg
Search: This sheet
Search using regular expressions
Replace all.
3
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:
LEFT ALT + D + E
And as already mentioned SPLIT formula:
=SPLIT("l,f,x,a,s,f"; ",")
=SPLIT(A1; ",")
For a range/array it would be:
=ARRAYFORMULA(IFERROR(...
3
You could split the cell's value into individual cells. Given that cell A1 contains the number 2113, put the following in cell B1:
=SPLIT(REGEXREPLACE("" & A1, "(\d)", "$1,"), ",")
This should result in cells B1:E1 containing each respective digit.
What the formula does is three things:
The expression "" & A1 converts the input number 2113 to a
...
3
My problem was that Google Sheets did not recognise what I was typing in as a date (24.05.2017) so when I changed it to 24/05/2017 it dragged consecutive dates immediately without having to highlight any other cells.
3
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 alternative is to use the arithmetic operators (+,-,/,*).
If it makes sense to assume that
all ranges are of the same size
there aren't blank cells between non-...
3
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&A:
How to use ARRAYFORMULA and SUM together to get a column with the sum of the preceeding columns?
Make a custom function
Limitations:
Only works when ...
3
A1:
=ARRAYFORMULA(JOIN("-",TEXT({"7/22","7/28"}+(COLUMN(A1)-1)*7,"M/DD")))
Drag to right.
Two dates are initialized and a column number*7 is added per each column.
TEXT to format dates " m/dd"
JOIN the 2 dates by -
2
Use the Google Sheets array handling features and FILTER to get the desired result
=FILTER({A:A,B:B,E:E;A:A,C:C,E:E;A:A,D:D,E:E},LEN({A:A;A:A;A:A}))
Note: If you spreadsheet use comma , as the decimal separator, replace the comma in the above formula by backslash \
2
Slight improvement to formula in this other answer so in case that A1 is blank or if it has an empty string it will return zero.
=IF(LEN(A1)=0,0,COUNTA(SPLIT(A1," "))
2
In array formulas instead of AND and OR functions use * and + respectively.
Example
AND: Instead of
=ArrayFormula(and(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4*B2:B4)
OR: Instead of
=ArrayFormula(or(A2:A4,B2:B4))
use
=ArrayFormula(A2:A4+B2:B4)
2
Yet another way:
=AVERAGE(ARRAY_CONSTRAIN(SORT(FILTER(A:A, ISNUMBER(A:A)), 1, 1), 7, 1))
Explantion:
filter out all numeric values
turn the column upside down (so that last values are above) by using sort()
limit the output of that column to 7 rows (using ARRAY_CONSTRAIN)
Take the average ...
EDIT: Sorry for misunderstanding. The above averages the ...
Only top voted, non community-wiki answers of a minimum length are eligible
