"Day 13: Excel Essentials Unveiled - Sharing Today's Insights on My Learning Adventure! 📊🚀 #ExcelSkills #LearningJourney"

EXCEL - 12

Data Cleaning formula :-

TRIM
PROPER
LOWER and UPPER
CLEAN
SUBSTITUTE
LEFT, RIGHT, MID
LEN
CLEAN and TRIM Combination
IF and ISNUMBER
IF and ISTEXT
IF and ISBLANK
TEXT and DATEVALUE

TRIM:
Removes extra spaces from text, leaving only single spaces between words.=TRIM(text)

PROPER:
Capitalises the first letter of each word in a text string.
=PROPER(text)

LOWER and UPPER:
Converts text to lowercase or uppercase, respectively.
=LOWER(text)
=UPPER(text)

CLEAN:
Removes non-printable characters from text.
=CLEAN(text)

SUBSTITUTE:
Replaces occurrences of a specified substring with another substring.
=SUBSTITUTE(text, old_text, new_text, [instance_num])

LEFT, RIGHT, MID:
Extracts a specified number of characters from the left, right, or middle of a text string.
=LEFT(text, num_chars)
=RIGHT(text, num_chars)
=MID(text, start_num, num_chars)

LEN:
Returns the number of characters in a text string.
=LEN(text)

CLEAN and TRIM Combination:
Combines the CLEAN and TRIM functions to remove non-printable characters and extra spaces from text.
=TRIM(CLEAN(text))

IF and ISNUMBER:
Checks if a value is a number and returns a specified result based on the condition.
=IF(ISNUMBER(value), result_if_true, result_if_false)

IF and ISTEXT:
Checks if a value is text and returns a specified result based on the condition.
=IF(ISTEXT(value), result_if_true, result_if_false)

IF and ISBLANK:
Checks if a cell is blank and returns a specified result based on the condition.
=IF(ISBLANK(cell), result_if_true, result_if_false)

TEXT and DATEVALUE:
Converts a text representation of a date to a serial number.
=DATEVALUE(TEXT(date_text, "mm/dd/yyyy"))

Best option after all the data is clean for data separation

Text to Column option is the best for data cleaning after we remove all the unwanted details in the datasets.

Flash fill ( Ctrl + E ) = In this you have to give an example and then just press flash fill.

These text functions in Excel are valuable for cleaning, formatting, and manipulating text data, providing flexibility and control over the presentation of text in your worksheets.
In summary, data validation is a powerful tool in Excel that enhances data accuracy and efficiency.
Follow me on this where every day will be added if i learn something new about it :- https://dev.to/nitinbhatt46

Thank you for your time.