Spreadsheet Tricks

Question 1

How would you calculate the number of days/months/years between two dates? (Function?)

Answer 1

DATEDIF(Start Date,End Date,unit)
units: D, M, Y

Question 2

What function would you use to determine the # of characters in a cell (for data cleaning purposes)

Answer 2

=LEN() returns a character count

Question 3

Cleanup: If you needed to break apart a fixed number of characters from the start or end of a string, what function could you use?

Answer 3

=LEFT(cell, # chars)
=RIGHT(cell, #chars)

Question 4

Cleanup: How would you split this data series into 3 spearate columns?
9823MR521
1231SE214

Answer 4

=LEFT(cell, 4) extract first 4 chars
=SPLIT(cell, 5, 2) extract two chars starting with fifth
=RIGHT(cell, 3) extract last 3 chars

Question 5

Cleanup: What function would you use to sequentially combine the value in cell A1 and A2 into a single cell with a space character between the values?

Answer 5

=CONCATENATE(A1,” “,A2)
=A1 & “ “ & A2

Question 6

Cleanup: What is the funtion to remove any leading, trailing, or repeated spaces in a string?

Answer 6

=TRIM(cell)

Question 7

Cleanup: What are two ways you might divide the following data into two separate columns?
12312-AABE
123-AFI

Answer 7

Text to Columns
or
=SPLIT(cell, “-“)