Common Google Spreadsheet Formulas

google_sheets.txt

1. Identifying Cells by Type in Google Spreadsheets
=if(index(split(D1, "::"),1) = "mobileapp", "Mobile", "Website")

-------


2. Get the root domain url of a cell in Google Spreadsheets
For sites with one TLD or one TLD and one subdomain: ie: xx.com or xx.xx.com
=iferror(join(".", index(split(A2, "."),2),index(left(split(A2, "."),3),3)), join(".", index(split(A2, "."),1),index(left(split(A2, "."),3),2)))
 
For sites with one TLD and two subdomains: ie xx.xx.com
=iferror(join(".", index(split(A20, "."),3),index(left(split(A20, "."),3),4)), join(".", index(split(A20, "."),1),index(left(split(A20, "."),3),2)))
 
For sites with one TLD and Three subdomains: xx.xx.xx.com
=iferror(join(".", index(split(A5, "."),4),index(left(split(A5, "."),3),5)), join(".", index(split(A5, "."),1),index(left(split(A5, "."),3),2)))
 
For sites with one TLD and Four subdomains: xx.xx.xx.xx.com
=iferror(join(".", index(split(A2, "."),5),index(left(split(A2, "."),3),6)), join(".", index(split(A2, "."),1),index(left(split(A2, "."),3),2)))
 
-------


3. How to count subdomains:
=COUNTA(split(A2, "."))-2

-------

4. Create a list of keywords from a different spreadsheet.
This formula will take contents in cell A1, import a seperate list of words and create a column of new words 

From a seperate spreadsheet:
=ArrayFormula(transpose(split(concatenate($A$1&" "&transpose(importrange("spreadsheeturl","'tabName'!B2:B7"))& char(9)),char(9))))

From a seperate tab in the same Google Spreadsheet:
=ArrayFormula(transpose(split(concatenate($A$1&" "&transpose('tabName'!B2:B7)& char(9)),char(9))))

From a seperate column in same tab of the same spreadsheet:
=ArrayFormula(transpose(split(concatenate($A$1&" "&transpose(B2:B7)& char(9)),char(9))))


-------

5. Concatenate Keywords Together
Example:
A1: Google Adwords Expert

Seperate list of words:
B2: Asheville NC
B3: Charlotte NC
B4: Greenville SC
B5: Charleston SC
B6: Columbia SC
B7: Raleigh NC

Becomes:
C2: Google Adwords Expert Asheville NC
C3: Google Adwords Expert Charlotte NC
C4: Google Adwords Expert Greenville SC
C5: Google Adwords Expert Charleston SC
C6: Google Adwords Expert Columbia SC
C7: Google Adwords Expert Raleigh NC

-------


6. Transform a cell into a broad match modified keyword.
A1: Google Adwords Expert
A2: =ArrayFormula(CONCATENATE(" +"&split('A1," ")))
 
Example:
Google Adwords Expert becomes +Google +Adwords +Expert

7. Get the total number of days in a month:
=DAY(DATE(YEAR(today()),MONTH(today())+1,0))

8. Get the current daily spend:
=spend/day(date(year(today()),month(today()), day(today())))

9. Get the required daily spend:
=budget/DAY(DATE(YEAR(today()),MONTH(today())+1,0))

10. Percent off spend:
=current/required

11. Days left in the current month:
=DAY(DATE(YEAR(today()),MONTH(today())+1,0))-day(date(year(today()),month(today()), day(today())))

12. Divide Spend by the total number of days so far in the year:
=C2/DATEDIF(date(year(today()),1,1),today(), "D")