Excel Functions for Data Cleaning

Excel Functions for Data Cleaning

Consider a scenario where you have a list of product codes such as "ABC-12345," and it is necessary to extract only the "ABC" portion. Alternatively, you might be dealing with a column of names from which you need to isolate the first name. Another common issue involves imported data containing extra spaces that disrupt your formulas. This is where Excel's text functions become invaluable. These functions enable you to:

• ·     Extract specific segments of text: Retrieve characters from the left, right, or middle.
• ·       Locate and identify text: Determine the position of particular characters or words.
• ·       Replace and substitute text: Update old text with new text or correct errors.
• ·       Combine and split text: Consolidate multiple pieces of text or divide them accordingly.
• ·       Modify text case: Convert text to uppercase, lowercase, or proper case.
• ·       Clean up text: Eliminate unwanted spaces and ensure data consistency.

Now, let's proceed with our detailed examples to better understand these functionalities.

Extracting Characters from Text – Precision Pulls!

These functions are your go-to for grabbing precise segments of a text string.

LEFT: Grabbing from the Start - The LEFT function extracts a specified number of characters from the beginning (left side) of a text string.

• Your Goal: Get the first few characters of an ID or date.
• Formula Syntax: =LEFT(text, num_chars)
• text: The cell containing the text you want to extract from.
• num_chars: The number of characters you want to extract from the left.

RIGHT: Pulling from the End -The RIGHT function extracts a specified number of characters from the end (right side) of a text string.

• Your Goal: Get the file extension from a filename or the last digits of a code.
• Formula Syntax: =RIGHT(text, num_chars)
  
  
  

MID: Slicing from the Middle - The MID function extracts a specified number of characters from the middle of a text string, starting at a specific position.

• Your Goal: Extract a specific part of a product ID or a middle initial.
• Formula Syntax: =MID(text, start_num, num_chars)
• start_num: The position of the first character you want to extract.
• num_chars: The number of characters you want to extract.

LEN: Counting Characters - The LEN function simply returns the total number of characters in a text string.

• Your Goal: Check the length of a string, often used with other functions to create dynamic extractions.
• Formula Syntax: =LEN(text)

Finding and Searching Text – The Detective Work!

These functions help you locate specific parts of a text string, often used in conjunction with LEFT, RIGHT, or MID to make your extractions more flexible.

1. FIND: The Case-Sensitive Search The FIND function returns the starting position of a specified substring within a text string. It is case-sensitive.

• Your Goal: Find the location of a specific character (like a space or hyphen) so you can use it as a reference point for other extractions.
• Formula Syntax: =FIND(find_text, within_text, [start_num])
• find_text: The text you want to find.
• within_text: The text string you want to search within.
• [start_num] (optional): The character position at which to start the search. If omitted, it starts from 1.

SEARCH: The Flexible Search

Similar to FIND, the SEARCH function also returns the starting position of a specified substring within a text string, but it is case-insensitive. It also supports wildcard characters (* for any sequence of characters, ? for any single character).

• Your Goal: Find text without worrying about its capitalization, or use wildcards for more flexible searches.
• Formula Syntax: =SEARCH(find_text, within_text, [start_num])

    

Replacing and Substituting Text – Making Changes!

These functions allow you to modify existing text within a cell, from simple replacements to more complex substitutions.

SUBSTITUTE: Swapping Specific Text

The SUBSTITUTE function replaces specific text within a string with new text. It's great for fixing typos or standardizing terms.

• Your Goal: Change all occurrences of "old" to "new" within a text string.
• Formula Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
• text: The cell containing the text.
• old_text: The text you want to replace.
• new_text: The text you want to replace with.
• [instance_num] (optional): Which occurrence of old_text you want to replace. If omitted, all occurrences are replac

REPLACE: Changing Text by Position

The REPLACE function replaces a specific number of characters at a certain starting position within a string with new text.

• Your Goal: Change a part of a code that's always in the same position, regardless of its current content.
• Formula Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
• old_text: The cell containing the original text.
• start_num: The starting position of the characters you want to replace.
• num_chars: The number of characters you want to replace.
• new_text: The text you want to replace with.

Combining and Splitting Text – The Data Manipulators!

These functions are fantastic for restructuring your text data, either by merging multiple pieces or breaking a single string into many.

CONCAT: Simple Text Merging  The CONCAT function (or CONCATENATE in older Excel versions) combines multiple text values into one string.

• Your Goal: Join first and last names, combine data from different cells.
• Formula Syntax: =CONCAT(text1, [text2], ...)

TEXTJOIN: Merging with Delimiters The TEXTJOIN function combines multiple text values into one string, using a specified delimiter (a character that separates the values). It also has an option to ignore empty cells.

• Your Goal: Create a comma-separated list from a range of cells, skipping blanks.
• Formula Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
• delimiter: The character(s) to place between each text item.
• ignore_empty: TRUE to ignore empty cells, FALSE to include them.

TEXTSPLIT: Breaking Text Apart

The TEXTSPLIT function (available in Microsoft 365) splits text into rows or columns based on a delimiter. This is a game-changer for organizing data!

• Your Goal: Take a single cell with comma-separated values and put each value into its own cell.
• Formula Syntax: =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
• text: The text you want to split.
• col_delimiter: The delimiter(s) that indicate where to split text across columns.
• [row_delimiter] (optional): The delimiter(s) that indicate where to split text down rows

TEXTBEFORE: Getting Text Before a Point

The TEXTBEFORE function (available in Microsoft 365) extracts text that appears before a specified delimiter.

• Your Goal: Extract a first name from a full name, or a product code before a specific separator.
• Formula Syntax: =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [pad_with])

TEXTAFTER: Getting Text After a Point

The TEXTAFTER function (available in Microsoft 365) extracts text that appears after a specified delimiter.

• Your Goal: Extract a last name from a full name, or a category after a specific separator.
• Formula Syntax: =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [pad_with])

Changing Text Case – Formatting for Consistency!

These simple but powerful functions help you standardize the capitalization of your text data.

UPPER: All Caps!

The UPPER function converts all letters in a text string to uppercase.

• Your Goal: Make all names or codes uniform.
• Formula Syntax: =UPPER(text)

LOWER: All Lowercase!

The LOWER function converts all letters in a text string to lowercase.

• Your Goal: Standardize text for data matching or cleaning.
• Formula Syntax: =LOWER(text)

PROPER (Bonus!): Title Case!

While not explicitly in your image, PROPER is very useful. It converts the first letter of each word in a text string to uppercase and the rest to lowercase.

• Your Goal: Ensure names or titles have correct capitalization.
• Formula Syntax: =PROPER(text)

Cleaning Up Text – The Final Polish!

Messy data is a common problem. The TRIM function is your best friend for tidying up.

TRIM: Eliminating Extra Spaces

The TRIM function removes all extra spaces from text, leaving only single spaces between words and no leading or trailing spaces.

• Your Goal: Clean up inconsistent spacing from imported data.
• Formula Syntax: =TRIM(text)

You want to extract the first name, last name, and ensure proper capitalization and no extra spaces.

1. Clean up the Full Name: In C2, enter =TRIM(B2)
• Result for C2: John DOE (leading/trailing spaces removed)
2. Get the First Name (Proper Case): In D2, enter =PROPER(TEXTBEFORE(C2," "))
• Result for D2: John
3. Get the Last Name (Proper Case): In E2, enter =PROPER(TEXTAFTER(C2," "))
• Result for E2: Doe

Now your data is clean and consistently formatted

New Excel Superpowers!

You've just explored a powerful arsenal of Excel text extraction functions! Whether you need to pull specific characters, find text, replace values, combine or split strings, change case, or clean up messy data, these functions provide the precision and flexibility you need.

By understanding how LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, REPLACE, CONCAT, TEXTJOIN, TEXTSPLIT, TEXTBEFORE, TEXTAFTER, UPPER, LOWER, PROPER, and TRIM work, you can significantly enhance your data manipulation skills in Excel.

Don't be afraid to experiment with these functions and combine them for even more powerful results. The more you practice, the more intuitive they'll become.