Excel
Some Excel formula examples and tips. Click on a formula to copy it to the clipboard.
INDIRECT function - returns the reference specified by a text string
so cells moved down maintain reference to the same row
also can use the same formula on every row
test if 2 columns on the same row are the same
=IF(INDIRECT("A"&ROW())
use of INDIRECT prevents formula changing when moving cells e.g. cut/paste
in conditional formatting, AND() doesn’t work: multiply conditions instead
return string after last "/" character
=TRIM(RIGHT(SUBSTITUTE(A1,"/",
REPT returns the same number of spaces as the length of the string
SUBSTITUTE replaces each "/" with the REPT
RIGHT returns the right hand LEN() characters
TRIM removes the leading spaces
return string before first "@" character
convert an array of TRUE/FALSE values {,,,} to an array of 1’s and 0’s
e.g. for SUMPRODUCT (see next examples)
count number of cells in a range whose contents are longer than 2 characters
=SUMPRODUCT(--(LEN(A1:B10)>2))
test if string contains certain characters e.g. dash, inverted comma or space
=IF(SUMPRODUCT(--ISNUMBER(
SEARCH for an array of characters and return numbers for where each is found
--ISNUMBER returns 1 for each number and 0 for each not-a-number
SUMPRODUCT adds together all the 1’s and 0’s
IF the result is >0 then one of those characters exists
convert a mm/dd/yyyy string to a DATEVALUE
=DATEVALUE(MID(A1,7,4)&"/"&LEFT(A1,2)&"/"&(MID(A1,4,2)))
and convert a mm/dd/yyyy hh:mm:ss string to a DATEVALUE
then use cell format to display it as a date
note: DATEVALUE expects the date to be formatted based on your computer's regional settings - to avoid ambuguity (as in this example) use the order yyyy/mm/dd
convert a DATEVALUE to dd/mm/yyyy hh:mm string
=TEXT(A1,"dd/mm/yyyy hh:mm:ss")
=TEXT(A1,"dd/mm/yyyy hh:mm AM/PM")
blank cells from an IF( , ,"") are not actually empty (after a “Paste Values”). To convert them to actual blank cells either edit each blank cell (F2) and press Enter or:
select cells to convert 
Ctrl+F (Find dialogue) Alt+I (find all) Ctrl+A (select all found cells) Esc (close dialogue leaving blank cells highlighted) Del (delete the "blank" cells)
open a csv with no conversions (long numbers, leading zeroes, UK/US dates, etc)
new spreadsheet “Data” ribbon Get Data From File From Text/CSV Import Delimiter: Comma Load
This loads the file into a “Table” - to convert it to a “normal” spreadsheet:
“Query” ribbon Delete
“Table Design” ribbon Table Styles: "light"
Convert to Range
Use “Find and Replace” to replace a character with a newline/linefeed character:
“Find and Replace” “Replace with” press Ctrl+J (app only) or type Alt+010 (see Alt codes)
You can also use formulae to replace characters with a newline/linefeed:
note: “Wrap text” must be ticked in Format Cells Alignment
tilde “~” is an “escape” character which enables searches for literal wildcard characters like asterisks ~* and question marks ~? so to search for a tilde use: ~~
