Text and Date Functions

Introduction

Real data arrives messy: names with extra spaces, codes that need splitting, dates in odd formats. Text and date functions let you clean and reshape it without retyping. Functions behave the same way across the major programs, but a few names differ, so check your program's help.

Cleaning text

Suppose A2 contains the text Ana LOVELACE with extra spaces.

=TRIM(A2)                 → "Ana LOVELACE"
=UPPER(TRIM(A2))          → "ANA LOVELACE"
=PROPER(TRIM(A2))         → "Ana Lovelace"
=LEN(A2)                  → counts characters, including spaces

TRIM removes extra spaces at the start and end and repeated spaces inside.
UPPER, LOWER and PROPER change capitals.
You can nest functions: the inner one runs first.

Clean text matters because "Pen " with a trailing space does not match "Pen" in lookups.

Pulling out parts

For a code in B2 such as UK-2026-017:

=LEFT(B2, 2)        → "UK"
=MID(B2, 4, 4)      → "2026"
=RIGHT(B2, 3)       → "017"

MID(text, start, length) takes characters from a position. Counting begins at 1. To find a separator, use FIND or SEARCH, for example =FIND("-", B2) returns 3.

Joining text

The & symbol joins pieces:

=A2 & " " & B2            → "Ana Lovelace" (if A2 and B2 hold first and last names)
="Total: " & F10

Newer programs also have CONCAT and TEXTJOIN. To control how a number looks when joined, use TEXT:

="Revenue: " & TEXT(248.5, "0.00")      → "Revenue: 248.50"

Quick quiz

What does =LEFT('Spreadsheet', 6) return?

How dates work

A date is stored as a number: the count of days since a starting day, and you can calculate with it. Format the cell as a date to see it properly. You can type dates in the format your program expects, or build them safely with DATE:

=DATE(2026, 3, 15)        → 15 March 2026

The building blocks:

=YEAR(A2)  =MONTH(A2)  =DAY(A2)  =WEEKDAY(A2)
=TODAY()                  today's date, which changes every day

Date calculations

=B2 - A2                       days between two dates
=NETWORKDAYS(A2, B2)           working days between, excluding weekends
=EDATE(A2, 3)                  the same day three months later
=EOMONTH(A2, 0)                the last day of A2's month
=DATEDIF(A2, B2, "M")          whole months between

For example, the days from 1 March 2026 to 15 March 2026 is 14. DATEDIF exists in several programs but is not always listed in menus, so check your help pages.

Cautions

Day and month order differs between countries. Use DATE or an unambiguous format.
Dates typed as text will not calculate. Convert them with DATEVALUE or by re-entering.
TODAY() makes a sheet change each time it is opened, which may be unwanted in a report. Store a fixed date if the report must not change.
Time zones and times add complexity. Keep to dates when you can.

Cleaning a column at once

Write the formula once in an extra column, copy it down, check it, then copy and paste values only over the original if you want to keep the cleaned result. Keep the original data on another sheet in case you need it.

Try it yourself

Create a column with names that have extra spaces and mixed capitals, clean them with TRIM and PROPER, then build a "Name (code)" label by joining two columns. Add two dates and calculate the number of days and working days between them.

Summary

TRIM, UPPER, PROPER and LEN clean text, and LEFT, MID and RIGHT extract parts.
The & symbol and TEXT join and format values.
Dates are numbers, so you can subtract them, and DATE, EDATE, EOMONTH and NETWORKDAYS help with calendar calculations.