Advanced Excel · Beginner level

Beginner level · Module 2: Lookup and Logical Functions · Lesson 3 of 3

Text and Date Functions

Clean text and work with dates using functions, so data from different sources can be combined.

In this lesson you will

  • Clean and split text with functions
  • Calculate with dates and periods

Key concepts

TRIM, PROPER, UPPERTEXTSPLIT / LEFT / MIDTEXT and DATEEOMONTH and NETWORKDAYS

The lesson

Imported data is often messy. TRIM removes extra spaces, PROPER fixes capitalisation, and SUBSTITUTE replaces characters. TEXTSPLIT (or LEFT, MID and FIND in older versions) splits values such as 'Juba-Shop-01'.

Dates are numbers formatted to look like dates. DATE builds a date, TEXT(date, "mmm yyyy") formats it as text for labels, EOMONTH finds month ends and NETWORKDAYS counts working days.

Check that imported dates are real dates, not text: real dates align right by default and can be calculated with.

Step by step

  1. Clean a column of customer names.
  2. Split codes into parts.
  3. Convert text dates into real dates.
  4. Calculate payment due dates with EOMONTH.

Practice activity

Clean the imported customer and order data so lookups and summaries work.

Expected result: Clean names, split codes and real dates that calculate correctly.

Check your understanding

  1. How can you tell a date is stored as text?

    Show answer

    It does not calculate and often aligns left by default.

  2. What does EOMONTH return?

    Show answer

    The last day of a month a given number of months from a date.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.