Data cleaning in Excel is essential for ensuring accurate, consistent, and reliable data. It helps remove errors, duplicates, and inconsistencies, making data easier to analyze and interpret. Clean data supports effective decision-making, prevents formula errors, saves time, and enhances the professionalism of reports. It also enables smoother automation, reporting, and data integration across systems. In short, clean data is the foundation of trustworthy and efficient Excel work.
![]() |
Importance of Data Cleaning and how to use such formula in Excel |
Now you will find here some most powerful data cleaning formula with real-worlds example below.
1. TRIM()
=TRIM(text)
text – This is the cell or string from which you want
to remove extra spaces.
=UNIQUE(array, [by_col], [exactly_once])
array (required) – The range or array to extract
unique values from.
by_col (optional) – Set to TRUE to compare values by
column instead of rows (default is FALSE).
exactly_once (optional) – Set to TRUE to return only
values that appear exactly once.
3. UPPER(), LOWER(), PROPER()
UPPER
function converts all letters in a text string to uppercase. It is useful for standardizing text by making all character capital.LOWER
function converts all letters in a text string to lowercase. It helps in normalizing data that may be in mixed or uppercase.PROPER
function capitalizes the first letter of each word in a text string and converts all other letters to lowercase. It is commonly used for formatting names or titles.Example
=UPPER(text)
=LOWER(text)
=PROPER(text)
text – The cell or text you want to convert.
The SUBSTITUTE formula in Excel is used to replace
specific text within a string with new text. It is helpful when you want to
replace all or specific occurrences of a word or character in a cell
without changing the rest of the text.
Example
=SUBSTITUTE(text, old_text, new_text, [instance_num])
-
text
– The original text or cell reference. -
old_text
– The text you want to replace. -
new_text
– The text you want to insert in place ofold_text
. -
[instance_num]
– (Optional) If provided, only that instance of theold_text
is replaced. If omitted, all instances are replaced.
The TEXT formula in Excel is used to convert numbers, dates,
and times into formatted text using a specific format you define. This is
especially useful when you want to display days against the date in a more readable or
customized way without changing the actual value.
Example
=TEXT(value, format_text)
Parameters:
- value
– The numeric value, date, or time you want to format.
- format_text – The format code enclosed in quotation marks (e.g., "DDDD").
The FIND formula in Excel is used to locate the position of a specific character or substring within another text string. Unlike the SEARCH function, the FIND function is case-sensitive, which means it treats uppercase and lowercase letters as different.
Example
FIND(find_text, within_text, [start_num])
Parameters:
- find_text
– The text you want to find.
- within_text
– The text in which to search.
- [start_num] – (Optional) The position in the text to begin the search (default is 1).
7. LEFT(), RIGHT()
Purpose: Extracts characters from the beginning (left) of a
text string.
Example
=LEFT(text, [num_chars])
Parameters:
- text
– The string you want to extract characters from.
- num_chars
– (Optional) Number of characters to extract. Default is 1 if
omitted.
RIGHT Function
Purpose: Extracts characters from the end (right)
of a text string.
=RIGHT(text, [num_chars])
Parameters:
- text – The string you want to extract characters from.
- num_chars – (Optional) Number of characters to extract. Default is 1 if omitted.
=CONCATENATE("Excel", " ",
"Formula")
Parameters:
- text1, text2, – The text items (or cell references) you want to join. You can use up to 255 text items.
9. TEXTJOIN ()
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Parameters:
- delimiter – The character(s) to insert between each text item (e.g., " ", ",", "-").
- ignore_empty – TRUE to skip empty cells, FALSE to include them.
- text1, text2, ... – The text items or ranges to join.
10. TEXTSPLIT ()
The TEXTSPLIT formula in Excel is used to split a
text string into multiple cells based on a specified delimiter. It
breaks one cell’s content into separate parts (columns or rows), making it
easier to organize and analyze data.
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Parameters:
- text
– The text you want to split.
- col_delimiter
– The character(s) to split the text into columns.
- [row_delimiter]
– (Optional) Character(s) to split the text into rows.
- [ignore_empty]
– (Optional) TRUE to ignore empty values, FALSE to keep them.
- [match_mode]
– (Optional) 0 for exact match (default), 1 for case-insensitive
match.
- [pad_with] – (Optional) Value used to fill blank cells if the results are uneven.
Thank you for taking the time to read this blog. I hope you found it helpful and informative. If you enjoyed it or learned something new, feel free to share it with others. Stay tuned for more Excel tips and tricks to boost your productivity!