Exported data is rarely clean. Names have extra spaces, codes are joined together, and dates arrive as text. Excel’s text functions fix most of this without manual editing, and they are a common part of data analyst tests.
Cleaning
=TRIM(A2)removes extra spaces, including double spaces between words.=CLEAN(A2)removes non-printing characters that come from some systems.=PROPER(A2),UPPERandLOWERfix capitalisation.
Combine them: =PROPER(TRIM(A2)) tidies a list of names in one step.
Extracting parts
=LEFT(A2, 3): the first three characters, such as a city code.=RIGHT(A2, 4): the last four, such as a year.=MID(A2, 5, 2): two characters starting at position 5.=LEN(A2): the length, useful for spotting phone numbers with missing digits.
Finding a position
=FIND("@", A2) returns where the @ sits in an email. To get the domain: =MID(A2, FIND("@", A2) + 1, 100). Use SEARCH instead of FIND when case should not matter.
Splitting with TEXTSPLIT
In Microsoft 365, =TEXTSPLIT(A2, ",") splits “Python,SQL,Excel” into three cells. TEXTBEFORE and TEXTAFTER are even simpler: =TEXTAFTER(A2, "@") gives the email domain. In older Excel, use Data, then Text to Columns.
Joining
=TEXTJOIN(", ", TRUE, B2:D2) combines cells with a separator and skips blanks. The & operator joins simple pieces: =A2 & " - " & B2.
Numbers stored as text
Values that look like numbers but are left-aligned are often text. =VALUE(A2) converts them, and =TEXT(B2, "dd-mmm-yyyy") formats numbers or dates as text for labels.
Related: data cleaning in Excel, Excel formulas every data analyst should know and Excel IF and IFS.
Excel, SQL and Power BI are the core of our 5-month Data Analytics course in Bangalore.
Book a free demo class at our BTM Layout centre, and ask a counsellor for batch details.
