📊 Lesson 2.3: Text & Date Functions — TEXTJOIN, LEFT/RIGHT/MID & Working with Dates

Combining Text: CONCAT & TEXTJOIN

Joining pieces of text into one is called concatenation, and it comes up constantly: build a full name from first and last, make a label like "Invoice #1024," or assemble an address. The simplest tool is the ampersand operator, &, which glues text together: =A2&" "&B2 joins A2, a literal space (in quotes), and B2 — so "Ada" and "Lovelace" become "Ada Lovelace." Any text you type literally goes in quotes; the space between the names is the classic thing people forget.

For a cleaner, more readable version there's CONCAT, which joins whatever cells and ranges you give it: =CONCAT(A2, " ", B2) does the same as the ampersand version. (You may see an older function called CONCATENATE — it still works but is being retired in favor of CONCAT.) Where CONCAT shines over & is that it can accept a whole range at once.

The real star is TEXTJOIN, which solves the annoying part of concatenation: repeating the separator. It takes three kinds of input — a delimiter (what to put between each piece), whether to ignore empty cells (TRUE or FALSE), and then the text to join. So =TEXTJOIN(", ", TRUE, A2:A5) joins four cells with a comma-and-space between each, skipping any blanks — producing "apple, pear, plum" without a trailing comma or a gap where an empty cell was. That ignore-empties flag is what makes TEXTJOIN so much nicer than gluing things with & by hand.

Splitting Text: LEFT, RIGHT & MID

Just as often you need to extract part of a text value — the first name out of a full name, an area code out of a phone number, a category prefix out of a product code. Three functions pull characters from a string by position:

• LEFT(text, n) — takes the first n characters from the left. =LEFT("Excel", 2) gives "Ex".
• RIGHT(text, n) — takes the last n characters from the right. =RIGHT("Excel", 2) gives "el".
• MID(text, start, n) — takes n characters starting at position start (counting from 1). =MID("Excel", 2, 3) gives "xce".

On their own these are rigid — they need to know exactly how many characters to grab. Their power comes from pairing them with FIND (or SEARCH), which locates a character within text, and LEN, which measures length. For example, to pull the first name out of "Ada Lovelace" in A2, you find the space and take everything before it: =LEFT(A2, FIND(" ", A2) - 1). Reading it: "find the position of the space, then take that many characters minus one (to drop the space itself) from the left." To get the last name, you take everything after the space: =MID(A2, FIND(" ", A2) + 1, LEN(A2)) — start just past the space and grab the rest. These two formulas are the classic "split a full name" pattern, and you'll build them in the project.

Cleaning Text: LEN, TRIM & Changing Case

Real-world data is messy: trailing spaces, ALL CAPS, or inconsistent capitalization from different people typing entries. A few functions tidy all of that.

TRIM is the unsung hero of data cleanup. Invisible extra spaces are a leading cause of "why won't this match?" bugs — a lookup fails because one cell has a trailing space you can't see. LEN helps you spot them: if =LEN(A2) is larger than the visible characters, there's hidden whitespace. PROPER is perfect for standardizing names typed in a mix of cases, though watch it with names like "McDonald" (it becomes "Mcdonald") and acronyms — PROPER follows a simple first-letter rule, not real-world exceptions.

These functions combine freely. To clean and standardize in one shot, nest them: =PROPER(TRIM(A2)) strips stray spaces and then fixes the capitalization. Building from the inside out — TRIM runs first, then PROPER works on its cleaned result — is exactly the nesting idea from last lesson.

The Secret of Dates: Serial Numbers

Here's the single most useful thing to understand about dates in Excel, and it explains almost everything else: Excel stores every date as a plain number. Specifically, it counts the number of days since a starting point — day 1 is January 1, 1900. So the date January 1, 2000 is stored internally as the number 36526, and today is just a bigger number. What you see as "9/15/2026" is that underlying number wearing a date format. This is called the serial number system.

Once you know that, date math stops being mysterious. Because a date is a number, you can subtract dates to get the days between them: =B2-A2 where both are dates gives a count of days. You can add days to a date: =A2+30 is 30 days after A2. And =A2-TODAY() (from last lesson) tells you how many days until A2 — negative if it's already past. It all works because underneath, you're just doing arithmetic on numbers. Times work the same way: they're stored as the fraction of a day, so 6:00 AM is 0.25 (a quarter of the way through the day).

Date Functions & TEXT()

A family of functions builds, breaks apart, and shifts dates. Here are the everyday ones:

DATE is how you build a real date from separate year, month, and day numbers — handy when those pieces live in different cells: =DATE(C2, D2, E2). The trio YEAR, MONTH, and DAY do the reverse, extracting each part. EDATE is a small gem for anything on a monthly cycle — a subscription renewal three months out is =EDATE(A2, 3), and one month ago is =EDATE(A2, -1) — it even handles month lengths correctly.

DATEDIF — the useful, hidden one

DATEDIF gives the difference between two dates in the unit you choose: "d" for days, "m" for whole months, "y" for whole years. =DATEDIF(A2, B2, "y") is the classic way to compute a whole number of years — an age or a tenure. Here's the quirk worth knowing: DATEDIF is a hidden function — Microsoft kept it for compatibility with older spreadsheets, and although it now has a page on Microsoft Support, Excel won't offer it in autocomplete or the Insert Function list. It still works reliably; you just have to type the whole thing yourself, and the start date must be earlier than the end date. For plain days, =B2-A2 is simpler; reach for DATEDIF when you want whole months or years.

TEXT() — format a value as text

TEXT(value, format) converts a number or date into text formatted exactly how you want, using a format code. It's the bridge between the serial-number world and readable labels. =TEXT(A2, "mmmm d, yyyy") turns a date into "September 15, 2026"; =TEXT(A2, "dddd") gives the weekday name like "Tuesday"; =TEXT(B2, "$#,##0.00") turns a number into "$1,234.50" as text. This is especially useful when concatenating — because gluing a raw date with & shows its ugly serial number, but ="Due "&TEXT(A2, "mmm d") produces a clean "Due Sep 15." Remember that TEXT's output is text, so you can't do further math on it — use it for display, not calculation.

Flash Fill vs Formulas

Excel has a genuinely clever shortcut for text cleanup called Flash Fill. Instead of writing a formula, you just show Excel an example of what you want, and it figures out the pattern and fills the rest. Type "Ada" next to "Ada Lovelace," start typing the next first name, and Excel offers to complete the whole column — first names extracted, no formula in sight. You accept with Enter, or trigger it deliberately with Ctrl+E. It's brilliant for one-off cleanups: splitting names, reformatting phone numbers, combining columns, fixing capitalization.

So when should you use Flash Fill versus a formula? The deciding question is whether the data will change. Flash Fill produces static values — a snapshot. If the source data updates, the Flash Fill results do not follow; you'd have to run it again. A formula like =PROPER(TRIM(A2)) is live — change the source and the result updates instantly, and it works on new rows you add. Use Flash Fill for quick, one-time tidying of a fixed list; use a formula when the data is ongoing or when you want the cleanup to stay correct automatically.

🎯 Project: Clean Up a Messy List

Time to rescue some genuinely messy data — the kind you'll meet constantly in the wild. You'll take a contact list with jumbled full names, inconsistent capitalization, stray spaces, and a due-date column, and turn it into clean, split, standardized data with a live "days until due" figure. This is the data-cleanup skill that makes every later stage — analysis, PivotTables, dashboards — actually trustworthy.

   A                 B          C                    D
1  Full Name         Due Date   Clean Name           First
2    ada LOVELACE    9/20/2026  =PROPER(TRIM(A2))    =LEFT(C2,FIND(" ",C2)-1)
3  Grace HOPPER      10/5/2026  =PROPER(TRIM(A3))    =LEFT(C3,FIND(" ",C3)-1)

   E                              F              G
1  Last                          Days Left      Reminder
2  =MID(C2,FIND(" ",C2)+1,LEN(C2))  =B2-TODAY()  ="Due "&TEXT(B2,"mmm d")

Why it works:
- TRIM removes stray spaces; PROPER fixes capitalization.
- FIND(" ",C2) locates the space; LEFT grabs before it, MID grabs after.
- B2-TODAY() works because dates are stored as numbers.
- TEXT() formats the date as clean text for a label.

Best Practices for Text & Dates

✅ Do's

• TRIM imported data first. Invisible spaces cause mysterious lookup and matching failures — clean before you analyze.
• Let dates be real dates. Enter them so Excel recognizes them as dates (right-aligned, usable in math), not as text — then all the date functions work.
• Use TEXT() when combining a date or number into a label, so you get "Due Sep 15" instead of a raw serial number.
• Choose Flash Fill for one-off cleanups and formulas for living data — match the tool to whether the data will change.

❌ Don'ts

• Don't assume PROPER handles every name. "McDonald" and "van der Berg" break its simple rule — spot-check and fix by hand.
• Don't glue a raw date with & — you'll get its serial number. Wrap it in TEXT().
• Don't expect autocomplete to offer DATEDIF — it's hidden from the list; type the whole thing, oldest date first.
• Don't do further math on TEXT() output — it's text, not a number.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You can now reshape and repair data, not just calculate with it — joining and splitting text, standardizing messy entries, and doing genuine date arithmetic once you know dates are secretly numbers. That completes the Formulas stage of the course: you can get clean numbers in, make them calculate, and clean up the words and dates around them. Everything you build from here rests on this foundation.

❓ Common Questions at This Stage

🔭 Looking Ahead

That wraps up Module 2 and the whole Formulas stage. Next we move into Analysis. In Lesson 3.1: Sorting, Filtering & Excel Tables you'll start asking your data questions — ordering it, narrowing it down to just what you want to see, and turning a plain range into a structured Excel Table that supercharges everything you've learned so far.

✅ Before the Next Lesson

• Keep your cleaned-up list — you'll sort and filter data like it next
• Make sure you're comfortable nesting functions (like PROPER(TRIM(...)))
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• TEXTJOIN function — Microsoft Support
• Date and time functions (reference) — Microsoft Support
• Using Flash Fill in Excel — Microsoft Support