Lesson 4 of 8 · 7 min · Formulas

Text and date functions for cleaning data

Covers: Microsoft 365 Excel. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • Clean text with TRIM, CLEAN, UPPER, LOWER and PROPER
  • Cut text apart with LEFT, RIGHT, MID, FIND and TEXTSPLIT
  • Join text with &, TEXTJOIN and CONCAT
  • 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.

Tidying text

=TRIM(A2)       removes leading, trailing and repeated spaces
=CLEAN(A2)      removes non-printing characters (common in pasted data)
=UPPER(A2)  =LOWER(A2)  =PROPER(A2)     "asha kumar" becomes "Asha Kumar"
=LEN(A2)        number of characters
=SUBSTITUTE(A2, "-", "")                 replaces text

A standard cleaning formula for a name column: =PROPER(TRIM(CLEAN(A2))). Imported data often has a non-breaking space (character 160) that TRIM does not remove: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).

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/2025 is 3 April in India and 4 March in the US. Convert with DATE, 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 TRIM does 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 TEXT output in calculations.

Try it yourself

  1. Clean a column of messy names (extra spaces, mixed case) with a single formula.
  2. Split a@b.com addresses into username and domain two ways.
  3. Convert a column of amounts like ₹1,250 into numbers.
  4. Compute age in years from date of birth and the working days between order and delivery dates.
  5. 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, PROPER and SUBSTITUTE; work in helper columns.
  • Cut with LEFT, MID, FIND, TEXTBEFORE, TEXTSPLIT; join with & or TEXTJOIN.
  • Convert text to numbers and dates before analysing.
  • Use EOMONTH, DATEDIF, NETWORKDAYS and TEXT for date work.

Going deeper

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.