intro to cleaning data

Handy formulas

Function Description Formula
  Get data or apply function to data from a different sheet =Sheet1!A1
  Apply function to a column (shift-select cells, function must be in top cell) Control-d
AVERAGE Calculates the average =AVERAGE(A2:A10)
MEDIAN  Finds the value (number) in the middle of a range. =MEDIAN(A2:A10)
  Calculate percent (A2 is the smaller number) =A2/B2*100
  Calculate percent change (B2 is the new number, A2 is the old number) =((B2/A2)-1)*100
Check for duplicates. =EXACT(A2:A10)
COUNTIF Counts the number of cells where a given value appears. (range, criteria) Criteria can be a number or text that appears in cells. This exmple uses a ZIP Code. =COUNTIF(A2:A10, 94705 )
Removes spaces from beginning and end of text. =TRIM(A2:A10)
Removes all non-printable characters from text. =CLEAN(A2:A10)
Joins text items into one text item — (&” “] adds a space between cells
Converts text to lowercase.
UPPER Converts text to uppercase.
Capitalizes the first letter in each word of a text value.
Formats a number as text with a fixed number of decimals.
MID Returns a specific number of characters from a text string starting at the position that you specify. Useful for converting nine-diget ZIP codes. (Cell, Start position, number of chars)
=MID(A2, 1,5)
Returns the rightmost characters from a text value.
LEFT Returns the leftmost characters from a text value.
Replaces or add characters (For example, adding digits back to ZIP code).
Substitutes old text with new text.
=SUBSTITUTE(A2:A10, ”Oregon”, “OR”)