Lesson 4 of 8 · 7 min · Formulas
Text and date functions for cleaning data
What you will learn
- Clean text with
TRIM,CLEAN,UPPER,LOWERandPROPER - Cut text apart with
LEFT,RIGHT,MID,FINDandTEXTSPLIT - Join text with
&,TEXTJOINandCONCAT - Convert text to real numbers and dates
- Calculate with dates:
EOMONTH,DATEDIF,NETWORKDAYS,TEXT
In simple words
Real data is messy. Names have extra spaces, cities are spelt three ways, and dates arrive as text that Excel will not add or sort. Before you can analyse anything, you clean it. Text functions are small tools for cutting, joining and tidying words, and date functions are tools for working out gaps, months and deadlines.
Words to know
- String: another word for a piece of text.
- Delimiter: the character that separates parts of a text, such as a comma.
- Serial number: the number Excel uses to store a date (days since 1 January 1900).
- Helper column: an extra column you add just to hold a cleaning step.
Cutting text apart
=LEFT(A2, 3) first 3 characters
=RIGHT(A2, 4) last 4 characters
=MID(A2, 5, 2) 2 characters starting at position 5
=FIND("@", A2) position of "@" (case sensitive; use SEARCH for case-insensitive)
Combine them. The domain of an email:
=MID(A2, FIND("@", A2) + 1, 100)
The part before the @: =LEFT(A2, FIND("@", A2) - 1).
In Microsoft 365 the newer functions are simpler:
=TEXTBEFORE(A2, "@") the part before
=TEXTAFTER(A2, "@") the part after
=TEXTSPLIT(A2, ",") splits "a,b,c" across cells (spills)
For older Excel, use Data > Text to Columns (split by delimiter or fixed width) or Flash Fill (Ctrl+E), which learns a pattern from an example you type.
Joining text
=A2 & " " & B2 first name, space, last name
=TEXTJOIN(", ", TRUE, A2:A6) joins a range with a delimiter; TRUE skips blanks
=CONCAT(A2, B2)
Text that should be numbers
A number stored as text shows left-aligned, may have a green triangle, and is ignored by SUM. Fixes:
=VALUE(A2)or=--A2(the double minus forces conversion).- Remove symbols first:
=VALUE(SUBSTITUTE(SUBSTITUTE(A2, "₹", ""), ",", "")). - Data > Text to Columns > Finish converts a whole column in one go.
- Multiply a copied cell of 1 using Paste Special > Multiply.
Check with =ISNUMBER(A2) and =ISTEXT(A2).
Dates
Excel stores a date as a number (1 January 2025 is 45658) and shows it with a format. That is why you can subtract dates. Useful functions:
=TODAY() today's date (recalculates)
=DATE(2025, 10, 1) builds a date from year, month, day
=YEAR(A2) =MONTH(A2) =DAY(A2) parts of a date
=WEEKDAY(A2, 2) 1 for Monday through 7 for Sunday
=EOMONTH(A2, 0) last day of the same month
=EOMONTH(A2, -1) + 1 first day of the same month
=EDATE(A2, 3) the same day, 3 months later
=B2 - A2 days between two dates
=DATEDIF(A2, B2, "Y") complete years between (also "M" months, "D" days)
=NETWORKDAYS(A2, B2) working days between, excluding weekends
=NETWORKDAYS(A2, B2, Holidays) and a range of holiday dates
DATEDIF works but is missing from Excel's autocomplete; it is documented as a compatibility function. Age in years: =DATEDIF(DOB, TODAY(), "Y").
Formatting numbers and dates as text
TEXT(value, format) returns text, useful for labels:
=TEXT(A2, "dd-mmm-yyyy") 05-Oct-2025
=TEXT(A2, "mmm yyyy") Oct 2025
=TEXT(A2, "ddd") Sun
=TEXT(B2, "#,##0") 1,25,000 may show as 125,000 (depends on settings)
Use a real date (not the text) in calculations; use TEXT only for display in a label or a pivot grouping column.
Dates that arrive as text
If 01/10/2025 is left-aligned, it is text. Check with ISNUMBER. Fix:
=DATEVALUE(A2)when the text matches your regional date format.- For a fixed layout like
20251001:=DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)). - Beware day-month versus month-day:
03/04/2025is 3 April in India and 4 March in the US. Convert withDATE, not by hoping the format matches.
Common mistakes
- Cleaning in the original column so you cannot undo. Use a helper column, then paste values.
- Forgetting that
TRIMdoes not remove non-breaking spaces. - Treating dates stored as text as real dates, so sorting and filters go wrong.
- Day and month swapped after an import.
- Using
TEXToutput in calculations.
Try it yourself
- Clean a column of messy names (extra spaces, mixed case) with a single formula.
- Split
a@b.comaddresses into username and domain two ways. - Convert a column of amounts like
₹1,250into numbers. - Compute age in years from date of birth and the working days between order and delivery dates.
- Create a "Month" helper column with
TEXT(date, "yyyy-mm")and use it in a summary.
Check yourself
What does TRIM do, and what does it not do?
It removes extra spaces at the start and end and repeated spaces inside text. It does not remove non-breaking spaces (character 160) or non-printing characters.
How do you take the text before an @ sign?
=LEFT(A2, FIND("@", A2) - 1), or =TEXTBEFORE(A2, "@") in Microsoft 365.
How do you find the last day of a month?
=EOMONTH(date, 0).
Why can't you add numbers that are stored as text?
Functions like SUM ignore text values. Convert them with VALUE, Text to Columns or multiplying by 1.
Interview questions
How would you clean a column of customer names that has extra spaces and inconsistent capitals?
Use =PROPER(TRIM(CLEAN(A2))) in a helper column, then copy and paste as values over the original once checked.
How do you calculate the number of working days between two dates?
=NETWORKDAYS(start, end, holidays), which skips weekends and a list of holidays.
How do you convert text dates to real dates?
Build them with DATE(year, month, day) after extracting parts with LEFT, MID and RIGHT, or use DATEVALUE when the text matches the regional format; then format the cells as dates.
Summary
- Clean with
TRIM,CLEAN,PROPERandSUBSTITUTE; work in helper columns. - Cut with
LEFT,MID,FIND,TEXTBEFORE,TEXTSPLIT; join with&orTEXTJOIN. - Convert text to numbers and dates before analysing.
- Use
EOMONTH,DATEDIF,NETWORKDAYSandTEXTfor date work.
Going deeper
- Excel functions by category: every text and date function, with the Excel version each needs.
- TEXT function: format codes for dates and numbers.
- DATE function: build real dates from year, month and day.
- Convert dates stored as text to dates: fixing text dates.
See how much of this you can explain
A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.