✨Basic Data Cleaning Functions in Excel (For Beginners)

✨Basic Data Cleaning Functions in Excel (For Beginners)

Hello friends 👋 Welcome back to Brundha_Sri Learning Hub!
Today, let’s start our Data Cleaning journey in Excel – the most important step for any Data Analyst.

👉 Did you know? 80% of analysis time goes into cleaning messy data before you can create dashboards or insights. That’s why Excel provides some powerful but simple functions to clean your dataset.

In this blog, we’ll learn 8 basic functions with real-life examples.

🔹 1. TRIM() – Remove Extra Spaces

👉 Meaning: Deletes all extra spaces in text (except single space between words).

Syntax:

=TRIM(text)

Example:
Raw Data → " John Smith "
Formula → =TRIM(A2)
Result → John Smith ✅

🔹 2. CLEAN() – Remove Hidden Characters

👉 Meaning: Removes non-printable characters (often appear when you copy-paste from websites, PDFs, or systems).

Syntax:

=CLEAN(text)

Example:
Raw Data → "Michael…Johnson"
Formula → =CLEAN(A2)
Result → MichaelJohnson ✅

🔹 3. PROPER() – Capitalize First Letters

👉 Meaning: Converts text so that each word starts with a capital letter.

Syntax:

=PROPER(text)

Example:
Raw Data → emma brown
Formula → =PROPER(A2)
Result → Emma Brown ✅

🔹 4. UPPER() & LOWER() – Change Text Case

👉 Meaning: Converts text to all uppercase or lowercase.

Syntax:

=UPPER(text)
=LOWER(text)

Example:
Raw Data → JohnSmith@GMAIL.com

  • =UPPER(A2) → JOHNSMITH@GMAIL.COM

  • =LOWER(A2) → johnsmith@gmail.com

🔹 5. SUBSTITUTE() – Replace Text or Symbols

👉 Meaning: Replaces specific text/character inside a string.

Syntax:

=SUBSTITUTE(text, old_text, new_text)

Example:
Raw Data → 987-654-3210
Formula → =SUBSTITUTE(A2,"-","")
Result → 9876543210 ✅

🔹 6. REPLACE() – Replace by Position

👉 Meaning: Replaces part of a text string based on position & length.

Syntax:

=REPLACE(old_text, start_num, num_chars, new_text)

Example:
Raw Data → 9876543210
Formula → =REPLACE(A2,4,3,"***")
Result → 987***3210 ✅

🔹 7. TEXT() – Format Numbers/Dates

👉 Meaning: Converts a number or date into a specific format.

Syntax:

=TEXT(value, format_text)

Examples:

  • =TEXT(25.0,"00") → 25

  • =TEXT(D2,"MMMM-YYYY") → January-2025

📝 Example Dataset

Before Cleaning (Messy Data)

Name (Raw) Phone (Raw) Email (Raw) Date (Raw)
john smith 987-654-3210 JohnSmith@GMAIL.com 01/01/25
EMMA BROWN (987)6543210 emma.brown@gmail.COM 2025-01-01
rahul sharma 987.654.3210 rahulsharma@ outlook.com 1-Jan-25

After Cleaning (Using Functions)

Name (Cleaned) Phone (Cleaned) Email (Cleaned) Date (Formatted)
John Smith 9876543210 johnsmith@gmail.com January-2025
Emma Brown 9876543210 emma.brown@gmail.com January-2025
Rahul Sharma 9876543210 rahulsharma@outlook.com January-2025

🎤 Wrap-Up

“So friends, these basic Excel functions – TRIM, CLEAN, PROPER, UPPER/LOWER, SUBSTITUTE, REPLACE, and TEXT – are your first toolkit to transform raw, messy data into neat, professional data.

📌 Try them on your names, phone numbers, and emails today. You’ll be amazed how quickly your dataset becomes analysis-ready!” 🚀

👉 In next, we’ll cover Intermediate Data Cleaning Functions like VALUE, LEN, LEFT, RIGHT, MID, SEARCH, etc. Stay tuned!


Comments

Post a Comment

Popular posts from this blog

Excel for Data Analysts: The Ultimate Beginner’s Guide

Image
Excel for Data Analysts: The Ultimate Beginner's Guide What is Excel? Microsoft Excel is a spreadsheet program that helps us to store, organize, analyze, and visualize data. It is part of the Microsoft Office suite and is widely used in almost every company, from small businesses to large enterprises. At its core, Excel works with rows and columns. Rows are numbered (1, 2, 3, …). Columns are labeled with letters (A, B, C, …). The intersection of a row and column is called a cell. For example: Cell A1 means Column A and Row 1. 👉  Excel has  1,048,576  rows and  16,384  columns (from A to XFD). That’s a lot of space for data! The Excel interface at a glance – understand Ribbon, Formula Bar, Active Cell, and Status Bar Key Parts of Excel Ribbon Menu The Ribbon is the toolbar at the top of Excel. It has different tabs such as: Home → Formatting, alignment, cut/copy/paste. Insert → Charts, tables, pictures, shap...
Read more

Learn Excel for Data Analytics: Step-by-Step Roadmap

Learn Excel for Data Analytics: Step-by-Step Roadmap Hello everyone, 👋 Welcome to  Brundha_Sri Learning Hub ! 🎉 If you’re starting your journey in Data Analytics, the very first tool you need to master is Excel . It’s the backbone of analysis, reporting, and even dashboards. 🥳The good news? We’re going to learn it together, step by step, in this blog series. Think of this as your personal roadmap . I’ll walk you through Excel from the very basics all the way up to advanced topics — with examples that analysts actually use in real projects. 🚀 Here’s what’s waiting for you in this series 👇 🔹 Beginner Topics😍 These are your foundation blocks. If you’re totally new to Excel, start here: Getting comfortable with Excel – rows, columns, cells, and workbook basics. Exploring the Ribbon menu – Home, Insert, Formulas, Data, Review, View. Formatting cells – play with colors, fonts, borders, and number formats. Basic formulas – =SUM() , =AVERAGE() , =MIN() , =MAX() an...
Read more

Exploring the Excel Ribbon Menu: A Complete Beginner’s Guide

Image
🧑‍💻 Exploring the Excel Ribbon Menu: A  Complete Beginner’s Guide Hello friends! 👋 Welcome back to Brundha_Sri Learning Hub 🎉 If you’ve just opened Excel, one of the first things you’ll notice is the Ribbon menu at the top. It’s like the control panel of Excel , where every button, tool, and option lives. Think of it like the dashboard of a car – you need to know what each control does to drive smoothly! 🚗💨 Today, we’ll explore the major Ribbon tabs one by one: Home, Insert, Formulas, Data, Review, and View . Along the way, I’ll explain what each tab means and give you real-world examples of when you’ll use them. 🏠 1. Home Tab This is the most commonly used tab – your “everyday toolbox.” ✨ Key Features: Font styles, size, bold/italic/underline Cell formatting (colors, borders, alignment) Copy, paste, cut Number formatting (currency, % etc.) Sorting & filtering data 💡 Real-time Example: Imagine you’re preparing a sales report for your manag...
Read more