🔤 Section 1: Text Cleaning Functions in Excel (1–10)
Text data in Excel often contains extra spaces, hidden characters, wrong letter casing, or messy formatting. Before analysis, we must clean it. Below are the Top 10 Text Cleaning Functions with detailed examples and use cases. Hover on each golden box to explore 👇
1. TRIM()
Formula: =TRIM(A2)
Example Input: ” John Doe “
Output: John Doe
Why use it?
Data copied from databases or websites usually has extra spaces. TRIM removes all unwanted spaces
(except single spaces between words), ensuring clean and uniform text.
2. CLEAN()
Formula: =CLEAN(A3)
Example Input: “Hello♦World”
Output: HelloWorld
Why use it?
When you paste text from PDFs or external systems, hidden line breaks or symbols often appear.
CLEAN removes all non-printable characters, making text usable for further processing.
3. SUBSTITUTE()
Formula: =SUBSTITUTE(A4,"-","")
Example Input: 123-456-7890
Output: 1234567890
Why use it?
Use SUBSTITUTE to replace or remove unwanted characters like hyphens, commas, or text fragments.
It’s often applied to clean phone numbers, IDs, or product codes.
4. REPLACE()
Formula: =REPLACE(A5,1,5,"")
Example Input: 12345XYZ
Output: XYZ
Why use it?
REPLACE modifies text by position. For example, you can remove the first 5 digits of a code
or replace part of a string with something new.
5. TEXT()
Formula: =TEXT(A6,"dd-mm-yyyy")
Example Input: 2025/09/01
Output: 01-09-2025
Why use it?
The TEXT function formats numbers and dates into a standard readable format.
It’s widely used to ensure consistent display of dates, percentages, or currency in reports.
6. PROPER()
Formula: =PROPER(A7)
Example Input: jAnE SMiTH
Output: Jane Smith
Why use it? Converts text to “Title Case”. Useful for names, city names, or any labels where capitalization needs to be standardized.
7. UPPER()
Formula: =UPPER(A7)
Example Input: jAnE SMiTH
Output: JANE SMITH
Why use it? Ensures text is in full uppercase. Best for IDs, country codes, or standardizing datasets where uniformity is important.
8. LOWER()
Formula: =LOWER(A7)
Example Input: jAnE SMiTH
Output: jane smith
Why use it? Converts text into lowercase. Commonly used for email addresses, website URLs, or coding where lowercase input is required.
9. CONCAT()
Formula: =CONCAT(A8,B8)
Example Input: Yogesh + Pandey
Output: YogeshPandey
Why use it? Joins multiple text values into one. Useful for combining first & last names, addresses, or merging column values.
10. TEXTJOIN()
Formula: =TEXTJOIN(" ",TRUE,A8,B8)
Example Input: Yogesh + Pandey
Output: Yogesh Pandey
Why use it? Works like CONCAT but more powerful — allows you to specify a separator (like space or comma) and ignore empty cells automatically.
🔢 Section 2: Number Cleaning Functions in Excel (11–20)
Numbers often appear with extra digits, decimals, wrong formats, stored as text, or even negatives. These 10 Excel formulas will help you clean, format, and prepare numerical data for analysis. Hover on each golden box to explore 👇
11. LEFT()
Formula: =LEFT(A2,5)
Example Input: EMP00123
Output: EMP00
Extracts the first n characters from the left. Useful for cleaning product codes, IDs, or fixed-length prefixes.
12. RIGHT()
Formula: =RIGHT(A2,3)
Example Input: EMP00123
Output: 123
Extracts the last n characters. Often used to isolate numeric parts or codes from longer strings.
13. MID()
Formula: =MID(A2,4,3)
Example Input: EMP00123
Output: 001
Extracts a substring from the middle of text. Very handy when IDs contain embedded numbers.
14. LEN()
Formula: =LEN(A3)
Example Input: ” 98765 “
Output: 7
Counts the total number of characters (including spaces). Useful to detect extra spaces or inconsistent lengths in codes.
15. VALUE()
Formula: =VALUE(A4)
Example Input: ” 2500 “
Output: 2500
Converts text numbers into actual numeric values. Essential when imported data is stored as “text” and cannot be calculated.
16. ROUND()
Formula: =ROUND(A5,2)
Example Input: 123.4567
Output: 123.46
Rounds a number to the specified decimal places. Useful for financial or reporting accuracy.
17. ROUNDUP()
Formula: =ROUNDUP(A5,0)
Example Input: 123.4567
Output: 124
Always rounds numbers upward, even if the fraction is small. Useful in billing, pricing, or ceiling calculations.
18. ROUNDDOWN()
Formula: =ROUNDDOWN(A5,0)
Example Input: 123.4567
Output: 123
Always rounds numbers downward. Good for truncating without decimals, e.g., floor values.
19. INT()
Formula: =INT(A6)
Example Input: 45.98
Output: 45
Returns only the integer part of a number (drops decimals). Common in inventory, counts, or whole number analysis.
20. ABS()
Formula: =ABS(A7)
Example Input: -500
Output: 500
Removes the negative sign and returns absolute value. Great for distance, balance, or error calculations.
📅 Section 3: Date & Time Cleaning Functions in Excel (21–30)
Dates and times often come in inconsistent formats, text values, or unaligned intervals. These 10 Excel functions help you standardize and analyze date-time data. Hover on each golden box to explore 👇
21. TODAY()
Formula: =TODAY()
Output: 01-09-2025
Returns the current system date. Helpful for building dynamic reports like “Orders as of Today”.
22. NOW()
Formula: =NOW()
Output: 01-09-2025 16:45
Returns current date and time. Useful for timestamps in logs or dashboards.
23. DAY()
Formula: =DAY(A2)
Input: 2025-09-01
Output: 1
Extracts the day number from a date. Commonly used to group data by day of month.
24. MONTH()
Formula: =MONTH(A2)
Input: 2025-09-01
Output: 9
Extracts month number (1–12). Often used to filter or summarize monthly data.
25. YEAR()
Formula: =YEAR(A2)
Input: 2025-09-01
Output: 2025
Extracts year from a date. Useful for yearly trend analysis or financial reports.
26. TEXT() – Month
Formula: =TEXT(A2,"mmmm")
Input: 2025-09-01
Output: September
Converts a date into a full month name. Great for dashboards that need user-friendly labels.
27. TEXT() – Day
Formula: =TEXT(A2,"ddd")
Input: 2025-09-01
Output: Mon
Converts date into weekday name. Useful to find patterns like sales by weekday.
28. EDATE()
Formula: =EDATE(A2,1)
Input: 2025-09-01
Output: 2025-10-01
Adds or subtracts months from a date. Helpful for calculating expiry or due dates.
29. EOMONTH()
Formula: =EOMONTH(A2,0)
Input: 2025-09-01
Output: 2025-09-30
Returns the last day of the month. Common in salary cycles or monthly closing reports.
30. DATEDIF()
Formula: =DATEDIF(A2,B2,"d")
Input: 2025-01-01 → 2025-09-01
Output: 244
Calculates the difference between two dates. Can return days (“d”), months (“m”), or years (“y”). Useful in age or duration calculations.
⚠️ Section 4: Error Handling & Data Validation in Excel (31–40)
Raw data often contains errors, blanks, wrong data types, or invalid entries. These 10 Excel formulas help you catch, replace, and validate data for a smooth analysis. Hover on each golden box to learn 👇
31. IFERROR()
Formula: =IFERROR(A2/B2,"")
Example Input: 10 ÷ 0 → #DIV/0!
Output: (Blank)
Catches errors and replaces them with a blank or custom message. Great for making dashboards error-free.
32. IFNA()
Formula: =IFNA(VLOOKUP(A2,Table,2,0),"Not Found")
Example Input: Lookup for “EMP009” (not in table)
Output: Not Found
Specifically handles #N/A errors in lookups.
Perfect for reporting missing IDs or products clearly.
33. ISERROR()
Formula: =ISERROR(A2)
Example Input: #DIV/0!
Output: TRUE
Checks if a cell has any error. Useful in conditional formatting to highlight error values.
34. ISNA()
Formula: =ISNA(A2)
Example Input: #N/A
Output: TRUE
Checks only for #N/A errors.
Handy when validating lookup functions like VLOOKUP or INDEX+MATCH.
35. ISNUMBER()
Formula: =ISNUMBER(A2)
Example Input: 2500
Output: TRUE
Checks whether a cell contains a numeric value. Useful when imported numbers are actually stored as text.
36. ISTEXT()
Formula: =ISTEXT(A2)
Example Input: “Hello”
Output: TRUE
Checks whether a value is text. Useful to validate email IDs, names, or text-only fields.
37. ISBLANK()
Formula: =ISBLANK(A2)
Example Input: (Empty cell)
Output: TRUE
Checks if a cell is blank. Helps spot missing entries in a dataset.
38. IF()
Formula: =IF(A2>50,"Pass","Fail")
Example Input: 45
Output: Fail
Validates data against conditions. Commonly used in grading, thresholds, or categorization.
39. AND()
Formula: =AND(A2>0,B2<100)
Example Input: A2=50, B2=80
Output: TRUE
Checks if multiple conditions are TRUE. Great for validating ranges (like marks between 0–100).
40. OR()
Formula: =OR(A2="Yes",B2>100)
Example Input: A2=No, B2=150
Output: TRUE
Checks if at least one condition is TRUE. Useful for flexible validations like “Yes” OR value > 100.
🔍 Section 5: Lookup & Unique Functions in Excel (41–50)
Lookup and uniqueness functions help you find, validate, and deduplicate data. These formulas are essential for merging datasets, cleaning duplicates, and handling missing values. Hover on each golden box to explore 👇
41. VLOOKUP()
Formula: =VLOOKUP(A2,Table,2,FALSE)
Example: Find Employee Name using Employee ID
Output: “John Doe”
Searches a value in the first column of a table and returns a matching value. Widely used for cleaning datasets by pulling related details.
42. HLOOKUP()
Formula: =HLOOKUP(A2,Table,3,FALSE)
Example: Find sales in row for a product
Output: 1200
Looks up values across the top row of a table. Less common than VLOOKUP, but useful in horizontal datasets.
43. INDEX()
Formula: =INDEX(A2:C10,3,2)
Example: Get value at Row 3, Column 2
Output: “Delhi”
Returns a value from a table using row and column numbers. Very powerful when combined with MATCH for flexible lookups.
44. MATCH()
Formula: =MATCH("Delhi",A2:A10,0)
Example: Find position of “Delhi” in list
Output: 3
Returns the position of a value in a range. Essential for dynamic referencing in large datasets.
45. INDEX + MATCH
Formula: =INDEX(B2:B10,MATCH(A2,A2:A10,0))
Example: Find Salary of Employee ID 102
Output: 50,000
Combination of INDEX & MATCH is more powerful and flexible than VLOOKUP. It allows both vertical and horizontal lookups.
46. XLOOKUP() (Excel 365)
Formula: =XLOOKUP(A2,ID_Column,Name_Column,"Not Found")
Example: Find employee name by ID
Output: “John Doe”
The modern replacement of VLOOKUP. Easier, faster, and supports left lookups + error handling.
47. UNIQUE() (Excel 365)
Formula: =UNIQUE(A2:A20)
Example: List with repeated city names
Output: [Delhi, Mumbai, Pune]
Returns a list of unique values. Ideal for deduplicating datasets.
48. SORT() (Excel 365)
Formula: =SORT(A2:A20,1,TRUE)
Example: Cities list
Output: [Delhi, Kolkata, Mumbai, Pune]
Sorts a range or array. Useful in cleaning to present ordered lists automatically.
49. FILTER() (Excel 365)
Formula: =FILTER(A2:C20,B2:B20="Delhi")
Example: Show all rows where City=Delhi
Output: Sub-table of Delhi employees
Filters data dynamically by condition. Replaces manual filtering for live dashboards.
50. TRANSPOSE()
Formula: =TRANSPOSE(A2:A5)
Example: Column of names
Output: Row of names
Converts vertical data into horizontal, or vice versa. Helpful in reorganizing messy datasets quickly.
🧹 Excel Data Cleaning Functions – Quiz (25 Questions)
Test your knowledge of the Top 50 Excel Cleaning Functions! Answer all 25 questions and check your score at the end.