Session 2 - Data Literacy: Beyond the Basics

Text Functions for Data Cleaning in Google Sheets

In education, data consistency is key. Inconsistent formatting of names, subjects, or other data can lead to inaccurate reports and wasted time. Google Sheets' text functions offer powerful tools to clean and standardize your data. This guide focuses on essential functions with real-world examples relevant to educators.

1. Understanding Text Functions

Text functions manipulate text strings within your spreadsheet. They're crucial for cleaning data, extracting information, and ensuring consistency.

2. Essential Text Functions:

• TRIM: Removes extra spaces from the beginning, end, and within a text string. Useful for cleaning up data entered with inconsistent spacing.

  Example: TRIM(" John Doe ") returns "John Doe".

• CLEAN: Removes non-printable characters (like line breaks) from text. Helpful for data imported from other systems.

  Example: CLEAN("John" & CHAR(10) & "Doe") (where CHAR(10) represents a line break) returns "JohnDoe".

• LOWER: Converts text to lowercase. Useful for standardizing text for analysis or comparison.

  Example: LOWER("John Doe") returns "john doe".

• UPPER: Converts text to uppercase. Useful for standardizing text.

  Example: UPPER("john doe") returns "JOHN DOE".

• LEFT: Extracts a specified number of characters from the left side of a text string.

  Example: LEFT("John Doe", 4) returns "John".

• RIGHT: Extracts a specified number of characters from the right side of a text string.

  Example: RIGHT("John Doe", 3) returns "Doe".

• MID: Extracts a specified number of characters from a text string, starting at a specified position.

  Example: MID("John Doe", 6, 3) returns "Doe".

• CONCATENATE: Joins two or more text strings together. Useful for combining data from different columns.

  Example: CONCATENATE("John", " ", "Doe") returns "John Doe". You can also use the ampersand (&) for concatenation: "John" & " " & "Doe"

• SPLIT: Divides text into multiple columns based on a specified delimiter (e.g., a comma, space, or other character).

  Example: SPLIT("John,Doe", ",") will create two columns: one with "John" and the other with "Doe".

3. Real-World Examples for Educators:

a) Standardizing Student Names:

Imagine you have a spreadsheet with student names entered inconsistently:

Here's how to clean it up:

1. TRIM: In a new column, use =TRIM(A2) (assuming "john doe" is in cell A2) to remove extra spaces.
2. LOWER or UPPER: In another column, use =LOWER(B2) (assuming the trimmed name is in B2) to convert names to lowercase for consistency.
3. SPLIT: If names are sometimes entered as "Last Name, First Name," use =SPLIT(C2,",") (assuming the lowercased name is in C2). This will split the name into two columns (Last Name and First Name).
4. CONCATENATE (Optional): If you want to combine the split names back into one column as "First Name Last Name", use =CONCATENATE(D2," ",E2) (assuming the first name is in D2 and last name in E2).

b) Extracting Information from Combined Columns:

Suppose you have a column containing subject codes and class names:

You can extract the subject code and class name separately:

1. LEFT: =LEFT(A2, 4) (assuming "MATH10A" is in A2) will extract the subject code ("MATH").
2. RIGHT: =RIGHT(A2, 3) will extract the class name ("10A").
3. MID: If the format was MATH-10-A you might use =MID(A2, 6, 2) to extract the grade level "10".

c) Cleaning Data from Different Sources:

When merging data from different systems, you might encounter inconsistencies in formatting. Text functions can help you standardize the data before analysis. For example, different systems might use different date formats. You can use text functions combined with date functions to convert these to a consistent format.

4. Tips for Effective Data Cleaning:

• Work in a Copy: Always work on a copy of your original data to avoid accidentally overwriting important information.
• Use Helper Columns: Create new columns to perform each step of your cleaning process. This makes it easier to track your changes and troubleshoot any errors.
• Test Your Formulas: Double-check your formulas to ensure they are producing the desired results.
• Automate: Once you have developed a cleaning process, you can often automate it by dragging the formulas down to apply them to all rows in your data.

By mastering these text functions, you can significantly improve the quality and consistency of your data, making analysis and reporting much more efficient and reliable. This guide provides a foundation. Experiment and combine these functions to tackle more complex data-cleaning challenges.