Excel Data Cleaning Fundamentals

Explore data cleaning in Excel by identifying columns, such as first name and address, detecting errors with a code function, and applying trim, clean, substitute, and concatenate to fix inconsistencies.

Identify anomalies in the first name column, including leading and trailing spaces, non printable characters, and case inconsistencies, using code, ascii, trim, clean, substitute, and proper functions.

Apply the CODE function to the first name column to reveal ASCII codes, detect unprintable characters and extra spaces, and highlight results with conditional formatting for later cleaning.

Learn to clean non-printable characters in Excel using the CLEAN and TRIM functions, applying CLEAN in a temporary column, filling down, and validating results by adjusting formulas.

Learn to apply the trim function in Excel to remove leading and trailing spaces, use a new column to verify results, and note unbroken spaces for the next video.

Use substitute and trim in Excel to replace non-breaking spaces (char 160) with a leading space (char 32), then trim to clean first name data.

Apply the proper function to format first name values and fix case inconsistencies; replace originals by copying, pasting values, deleting temporary columns, and restoring the dataset order.

Learn to clean the last name column by nesting data cleaning functions into a single formula. Identify leading and trailing spaces, non printable characters, non breaking spaces, and inconsistencies.

Identify unprintable characters and leading or trailing spaces in last name values using the code() function, then highlight and sort the results with conditional formatting to guide cleanup.

Learn to clean and standardize data in Excel by nesting clean, trim, substitute, and proper to remove non printable characters, fix spaces, and correct last name casing.

Apply the if function in the data cleaning exercise to perform logical tests that fix text strings, handle unprintable characters, and remove non-relevant data in the last name column.

Learn to detect and clean address data in excel using the code function to extract ascii values, highlight unprintable and space characters with conditional formatting, and sort by color.

Explore combining the if, or, trim, clean, substitute, and proper functions to clean addresses with non printable characters, replace them with spaces, then trim and format with proper case.

Remove non-printable characters in text strings with a function combo of trim, clean, and a nested if formula in Excel, using a temporary column to clean address values.

Clean passport numbers in Excel by removing spaces and non printable characters, then prefix with energy using concatenate, and apply steps with columns and nested if, left, and right functions.

Learn to clean a mobile phone number column with right and len, using nested ifs to enforce eleven digits and remove leading/trailing non printable characters and non breaking spaces.

Use the LEN function to check phone number lengths and adjust 10- and 13-digit entries by adding a leading zero or removing 2 3 4, using CONCATENATE and RIGHT.

Apply nested IF formulas in Excel with LEN, RIGHT, and CONCATENATE to clean phone numbers. If 10, append zero; if 13, add zero after trimming by LEN and RIGHT.

Learn to fix text errors in Excel by using trim, clean, substitute, and char-based methods to replace quotation marks with the value above, move spaces, and maintain caps.

Fix country column values with a nested if and or, trim leading and trailing spaces and non printable characters, and replace abbreviations via a reference table using a temporary column.

Learn to replace airport abbreviations with full names using VLOOKUP and IFERROR, ensuring exact match, cleaning spaces and non‑printable characters, and replacing data with values from a lookup table.

Learn to use iferror with vlookup to replace errors with a chosen value, such as the airport full name, by filling down and replacing the original column.