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
EXACT
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 )
TRIM
Removes spaces from beginning and end of text. =TRIM(A2:A10)
CLEAN
Removes all non-printable characters from text. =CLEAN(A2:A10)
CONCATENATE
Joins text items into one text item — (&” “] adds a space between cells
=CONCATENATE(A2&" ", B2)
LOWER
Converts text to lowercase.
=LOWER(A2:A10)
UPPER Converts text to uppercase.
=UPPER(A2:A10)
PROPER
Capitalizes the first letter in each word of a text value.
=PROPER(A2:A10)
FIXED
Formats a number as text with a fixed number of decimals.
=FIXED(E2,1)
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)
RIGHT
Returns the rightmost characters from a text value.
=RIGHT(A2,5)
LEFT Returns the leftmost characters from a text value.
=LEFT(A2,4)
REPLACE
Replaces or add characters (For example, adding digits back to ZIP code).
=REPLACE(A2,6,5,+"-3428")
SUBSTITUTE
Substitutes old text with new text.
=SUBSTITUTE(A2:A10, ”Oregon”, “OR”)