Excel · Lesson 6 of 8

Text & date functions

Cleaning text and working with dates: TRIM, PROPER, TEXT, LEFT, TODAY, DATEDIF and more.

Text

TRIM removes extra spaces; PROPER, UPPER, LOWER fix case.
LEFT, RIGHT, MID take part of a string; LEN counts characters; FIND locates text.
TEXTJOIN(", ", TRUE, range) joins with a separator; TEXT(value, "dd-mmm-yyyy") formats numbers/dates as text.
Data → Text to Columns and Flash Fill (Ctrl+E) split or reshape text without formulas.

Dates

Excel stores dates as numbers (days since 1900), so you can subtract them. TODAY(), EOMONTH(date, 0) (month end), NETWORKDAYS(start, end) (working days), DATEDIF(start, end, "y") (completed years).

Sample sheet & formulas

Type these in Excel or Google Sheets

     A                        B
1    "  aNU   kumar "           12-Mar-2024   (joining date)
2    "EMP-2026-0457"            04-Nov-2026   (report date)

C1:  =PROPER(TRIM(A1))
C2:  =RIGHT(A2, 4)
C3:  =MID(A2, 5, 4)
C4:  =LEN(TRIM(A1))
D1:  =B2-B1
D2:  =DATEDIF(B1, B2, "y") & " yrs " & DATEDIF(B1, B2, "ym") & " months"
D3:  =TEXT(B1, "dddd, dd mmm yyyy")
D4:  =EOMONTH(B2, 0)

Results

C1 = Anu Kumar
C2 = 0457
C3 = 2026
C4 = 9
D1 = 967   (days)
D2 = 2 yrs 7 months
D3 = Tuesday, 12 Mar 2024
D4 = 30-Nov-2026

Works in Microsoft Excel 365/2021 and, for almost every formula, in Google Sheets. Data is fictional.

Key points

TRIM + PROPER clean messy names.
Dates are numbers — subtract them for days.
DATEDIF gives years/months between dates.

Quick check

Which function removes extra spaces from text?

CLEAN TRIM LEN SUBSTITUTE

Practice task

From a list of full names, split first and last names using Flash Fill, then rebuild them as “LAST, First” with a formula.

Interview corner

How do you calculate someone's age or experience in years?

=DATEDIF(start_date, TODAY(), "y") returns completed years.

A date column is left-aligned and won't sort. Why?

The dates are stored as text. Convert with DATEVALUE, Text to Columns, or by fixing the source format.