Clean & Look Up›Core· 35 min read

Handy Text & Date Functions

Functions like CONCAT, LEFT, TRIM and TODAY tidy up messy text and work with dates.

What you will learn

Join and split text with functions
Clean up stray spaces with TRIM
Use TODAY and basic date maths

Why clean text matters

Real data is messy. Names have extra spaces, some are in CAPITALS, and a full name might need splitting into first and last. Excel has small text functions that fix all of this for you, so you never have to retype anything.

Joining text together

To stick pieces of text together (this is called concatenation), use the & sign or the CONCAT function. Say A2 is Asha and B2 is Khan:

Join the first name, a space, and the last name

=A2 & " " & B2

Note: Output: Asha Khan The & glued the two cells together with a space in the middle. Without the space you would get AshaKhan with no gap.

Pulling pieces out of text

Function	What it does	Example with Mumbai
`LEFT(text, n)`	First n characters	`=LEFT(A2,3)` gives Mum
`RIGHT(text, n)`	Last n characters	`=RIGHT(A2,3)` gives bai
`UPPER(text)`	Make it all CAPITALS	`=UPPER(A2)` gives MUMBAI
`LOWER(text)`	Make it all lowercase	`=LOWER(A2)` gives mumbai
`LEN(text)`	How many characters	`=LEN(A2)` gives 6

TRIM removes hidden spaces

A sneaky problem is extra spaces you cannot see, like a name with spaces around it. They break sorting and matching. TRIM removes spaces from the start and end:

Strip the stray spaces from before and after the name

=TRIM("   Asha Khan   ")

Note: Output: Asha Khan TRIM removed the leading and trailing spaces but kept the single space between the words. Always TRIM imported data before you analyse it.

Working with dates

Excel stores dates as numbers behind the scenes, so you can do maths with them. Two handy functions:

=TODAY() gives today’s date, and it refreshes every day.
=YEAR(A2), =MONTH(A2) and =DAY(A2) pull a part out of a date.

Because dates are numbers, you can subtract them. If A2 is a delivery date and you write =A2-TODAY(), you get the number of days until delivery.

Pull just the year out of today’s date

=YEAR(TODAY())

Note: Output: 2026 TODAY() gave the full date and YEAR() pulled out just the year. This is great for grouping records by year in a report.

Tip: Use TRIM and UPPER (or LOWER) together to standardise text before matching, like =UPPER(TRIM(A2)). Tidy, consistent text makes lookups and counts far more reliable.

Q. A cell contains the word laptop with extra spaces around it. Which function cleans it to just laptop?

Answer: TRIM removes spaces from the start and end of text. LEFT, LEN and UPPER do not remove the surrounding spaces.

✍️ Practice

Put a first name in A2 and a last name in B2, then join them with a space into C2.
Type a name with extra spaces around it and clean it with TRIM, then make it UPPER case.

🏠 Homework

Take a list of five full names and split each into a first-name column (with LEFT or Text to Columns) and confirm TRIM removes any stray spaces.