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

Real data arrives messy: names in one cell that should be two, dates typed six different ways, stray spaces that break your lookups. In this lesson you'll learn to fix messy text with formulas instead of retyping it by hand — and you'll finally understand the secret that makes dates behave: under the surface, a date is just a number.

📚 What You'll Learn

By the end of this lesson, you will be able to:

Combine text with the & operator and TEXTJOIN
Extract and clean pieces of text with LEFT, RIGHT, MID, LEN, FIND, TRIM, and the case functions
Explain why a date is really a serial number, and do date math
Use TODAY, DATE, YEAR/MONTH/DAY, EDATE, DATEDIF, and TEXT to format a date
Clean a messy contact list into tidy, analysis-ready columns

⏱️ Estimated Time: 50 minutes

🎯 Project: Take a messy name/contact list and clean it — split and recombine names, standardize case, strip spaces, and compute "days until" a date.

In This Lesson

Why Text Functions Matter

Imagine someone hands you a spreadsheet of 500 customers. The names are in a single column — "john SMITH", " Maria Garcia", "PROF. Lee Chan" — with inconsistent capitals, extra spaces, and titles jammed in. You need first and last names in separate columns, tidily capitalized. Retyping 500 rows by hand would take an afternoon and introduce new errors. Text functions do it in one formula you copy down the column — in seconds, with no typos.

This is one of the most practical, everyday skills in all of spreadsheets. Data almost never arrives in the shape you want; the difference between someone who dreads messy data and someone who shrugs and fixes it is a handful of text functions. And crucially, they work non-destructively: your formulas produce clean copies in new columns while the original stays untouched, so you can always check your work.

🧠 Mindset

Think of text functions as an assembly line for words. Each function does one small job — pull off the first few characters, trim the spaces, fix the capitals — and you chain them together to transform messy input into exactly what you need. You don't need to memorize dozens of them; you need to recognize the handful of jobs (extract, clean, combine) and know which tool does each.

Combining Text — & and TEXTJOIN

The simplest way to join text is the ampersand operator, &. It glues values together, exactly as written:

=A2&" "&B2     joins "John" and "Smith" into "John Smith"
=B2&", "&A2    joins them as "Smith, John"

Notice the " " — that's a literal space in quotes. Anything in quotes is text you're inserting between the cells. Without it you'd get "JohnSmith". The older function CONCATENATE (and its shorter alias CONCAT) does the same job, but the & operator is usually cleaner to read.

TEXTJOIN — the modern favorite

When you're joining many values with the same separator, TEXTJOIN is far tidier. Its arguments are a delimiter, whether to ignore empty cells, and then the range or values to join:

=TEXTJOIN(", ", TRUE, A2:D2)
   joins everything in A2:D2 with ", " between them,
   and TRUE means "skip any blank cells" (no double commas)

That TRUE for "ignore empty" is the reason people love TEXTJOIN: build a mailing address from Street, Apt, City, ZIP and any blank field simply vanishes instead of leaving an ugly gap.

✅ Pro Tip

Joining text is also how you build readable labels for a dashboard, like ="Total for "&A2&": "&TEXT(B2,"$#,##0"). Combining text with the TEXT function (coming up in the date section) lets you format numbers and dates right inside a sentence.

Extracting & Cleaning Text

Joining is only half the story. Just as often you need to pull pieces out of text or scrub it clean. Here are the workhorses:

Function	What it does	Example
`LEFT(text, n)`	First n characters	`=LEFT("Sheets",3)` → "She"
`RIGHT(text, n)`	Last n characters	`=RIGHT("Sheets",2)` → "ts"
`MID(text, start, n)`	n characters from a position	`=MID("Sheets",2,3)` → "hee"
`LEN(text)`	How many characters	`=LEN("Sheets")` → 6
`FIND(find, text)`	Position of a substring (case-sensitive)	`=FIND(" ","John Smith")` → 5
`TRIM(text)`	Remove extra spaces	`=TRIM(" hi you ")` → "hi you"
`UPPER / LOWER / PROPER`	Change case	`=PROPER("john smith")` → "John Smith"
`SUBSTITUTE(text, old, new)`	Replace all of one substring	`=SUBSTITUTE("a-b-c","-","/")` → "a/b/c"

The real power comes from combining them. To split "John Smith" into first and last names, you find the space, then take everything before and after it:

First name:  =LEFT(A2, FIND(" ", A2) - 1)
Last name:   =MID(A2, FIND(" ", A2) + 1, LEN(A2))

Read the first one aloud: "take the LEFT of A2, up to one character before the space." That's the whole trick — nest a FIND inside a LEFT or MID to cut text at a landmark.

💡 The easy way, too

For a one-time split, you don't even need formulas: select the column and use Data → Split text to columns, or the SPLIT function (=SPLIT(A2, " ")). Use formulas when the data keeps changing and you want the split to update automatically; use Split-text-to-columns for a quick one-off cleanup. We'll lean on both in the cleanup project of Lesson 7.3.

⚠️ Important Note: Always TRIM imported text before you compare or look it up. A trailing space you can't see ("Apple " vs "Apple") is the number-one cause of a lookup mysteriously returning #N/A. When something "should match but doesn't," suspect stray spaces first.

How Dates Really Work

Dates confuse people until they learn one secret: a date in Sheets is just a number wearing a costume. Internally, Sheets counts days from a starting point (December 30, 1899, is day 0). So January 1, 2026, is stored as the number 46023 — and Sheets simply displays that number as a date because the cell is formatted that way.

graph LR A["📅 What you see
Jan 1, 2026"] --> B["🔢 What is stored
the number 46023"] B --> C["➖ Date math works
subtract two dates to get days"] C --> D["🎭 Format decides display
same number, many looks"]

Two big consequences follow from this:

Date math is just arithmetic. Subtract one date from another and you get the number of days between them: =B2-A2. Add 30 to a date and you get the date 30 days later: =A2+30. No special functions needed.
The value and its display are separate. The same underlying number can show as "1/1/2026", "Jan 1", "Thursday", or "2026-01-01" depending only on the cell's date format (Format → Number → Date/Custom). Changing the format never changes the value.

⚠️ Watch Out

If a date you typed shows up left-aligned as plain text (or a subtraction gives a weird result), Sheets probably didn't recognize it as a date — often because of the regional date order (month/day vs day/month) in your Spreadsheet Settings. Real dates right-align by default. When in doubt, build a date safely with =DATE(year, month, day), which never depends on how the text was typed.

Handy Date Functions

Once you accept that dates are numbers, these functions become obvious tools:

Function	What it does
`TODAY()`	Today's date (updates each day). `NOW()` includes the time.
`DATE(y, m, d)`	Build a real date from parts — the safe way to make a date
`YEAR / MONTH / DAY`	Pull the year, month, or day number out of a date
`WEEKDAY(date)`	Day of the week as a number (handy for filtering weekends)
`EDATE(date, n)`	The date n months later or earlier (great for subscriptions)
`EOMONTH(date, n)`	The last day of the month, n months away
`DATEDIF(start, end, unit)`	Difference in whole years, months, or days — good for ages
`TEXT(value, format)`	Turn a date or number into formatted text, e.g. for a label

A few everyday recipes:

Days until a deadline:      =A2-TODAY()
Someone's age in years:     =DATEDIF(A2, TODAY(), "Y")
Renewal date, one year on:  =EDATE(A2, 12)
A friendly label:           =TEXT(A2, "dddd, mmm d")   → "Thursday, Jan 1"

💡 Pro Tip

DATEDIF is a slightly hidden, quirky function (it isn't in the autocomplete list and has some edge-case oddities with the "MD" unit), but for a clean age or tenure in whole years it's the simplest tool. If you ever need day-accurate precision, remember you can always fall back to plain subtraction and divide.

🎯 Project: Clean a Contact List

Time to put text and date functions to work on the kind of mess you'll meet in real life. You'll turn a jumbled contact list into tidy, analysis-ready columns — the exact skill that makes lookups and pivots work later.

🏋️ Tidy the messy contacts

Objective: Split names, standardize them, strip spaces, and add a "days until renewal" column using date math.

Instructions (about 15 minutes):

(3 min) On a new tab, type a small messy dataset in column A (Full Name) and column B (Renewal Date): e.g. " john SMITH", "Maria Garcia ", "lee chan"; and a few dates a week or two out.
(3 min) In column C, clean the name: =PROPER(TRIM(A2)). Copy it down. Watch the capitals and spaces fix themselves.
(4 min) In D and E, split the cleaned name: First = =LEFT(C2, FIND(" ", C2)-1), Last = =MID(C2, FIND(" ", C2)+1, LEN(C2)).
(2 min) In F, recombine as "Last, First": =E2&", "&D2. Confirm it matches the split.
(3 min) In G, compute days until renewal: =B2-TODAY(). Format G as a plain number, not a date. In H, add a friendly label: =TEXT(B2,"mmm d").

💡 Hint — the column layout

A: Full Name (messy)   B: Renewal Date
C: =PROPER(TRIM(A2))              (clean name)
D: =LEFT(C2,FIND(" ",C2)-1)       (first)
E: =MID(C2,FIND(" ",C2)+1,LEN(C2))(last)
F: =E2&", "&D2                     (Last, First)
G: =B2-TODAY()                    (days until — format as number)
H: =TEXT(B2,"mmm d")              (friendly date label)

If a split errors, the row probably has no space (a single-word name) — a good reminder that real data always has exceptions. We handle those gracefully with IFERROR in Lesson 4.3.

✅ Project Completion Checklist

Names are cleaned with TRIM and PROPER
First and last names are split into their own columns
A "Last, First" column recombines them correctly
A "days until" column uses date subtraction and shows a plain number
A friendly date label uses TEXT

🎯 Quick Quiz

Question 1: Why can you subtract one date cell from another to get the number of days between them?

Question 2: A lookup keeps returning #N/A even though the value "clearly matches." What's the first thing to suspect?

Best Practices for Text & Dates

✅ Do's

Clean into new columns. Keep the messy original; put formula results beside it so you can verify.
TRIM imported text before comparing or looking it up.
Build dates with DATE() when typing is ambiguous, and check your locale's date order.

❌ Don'ts

Don't retype data by hand when a formula can transform it — it's slower and error-prone.
Don't confuse a date's format with its value. Reformatting never changes the number underneath.
Don't assume every row is well-formed. Single-word names and blank cells will break naive splits.

💡 Pro Tips

Once your cleaned columns look right, you can "freeze" them by copying and using Paste special → Values only, so they no longer depend on the messy source.
TEXT is your friend for building readable dashboard labels that mix words, numbers, and dates.

📓 Learning Journal

Keep a learning journal as you work through this course — a document, a note, or a tab in your own spreadsheet. After each lesson, take a few minutes to write down:

Key concepts you learned
Techniques that clicked for you
Questions or confusion points to revisit
Ideas you want to try in your own sheets
Your progress and feelings about learning this

✍️ This lesson's prompt: Think of a real place you've seen messy text or dates — a contact export, a downloaded report, a form's responses. Which one or two functions from this lesson would tidy it fastest? Jot down the formula you'd try.

📝 Lesson Summary

🎓 Key Takeaways

Join text with the & operator or TEXTJOIN (which can skip blank cells).
Extract and clean with LEFT/RIGHT/MID, LEN, FIND, TRIM, PROPER/UPPER/LOWER, SUBSTITUTE — chain them for real transformations.
A date is a serial number, so date math is just arithmetic and the format only changes how it looks.
TODAY, DATE, EDATE, DATEDIF, and TEXT handle the everyday date jobs.

🎉 What You've Accomplished

You can now take the kind of messy, real-world data that stops most people cold and reshape it into clean, consistent columns — and you understand dates well enough that they'll never mystify you again. That closes out the Formula & Function Foundations module: you now have the whole engine, from references to functions to text and dates.

❓ Common Questions at This Stage

Should I use formulas or "Split text to columns"?

Both are right in different situations. Use Split text to columns (or SPLIT) for a quick, one-time cleanup. Use formulas when the source data will keep changing and you want the cleaned columns to update automatically.

My date subtraction shows a date instead of a number of days.

Sheets copied the date format onto the result. Select the result cell and set its format to Number (Format → Number → Number). The value is correct; only the costume was wrong.

Why isn't DATEDIF in the autocomplete list?

It's a legacy function kept for compatibility, so Sheets doesn't suggest it, but it still works if you type it. For whole-year ages and tenures it's the simplest option.

🔭 Looking Ahead

In the next lesson — Lesson 3.1: Sorting, Filtering & Filter Views — we start Module 3, Working with Data. Now that you can enter, calculate, and clean data, you'll learn to wrangle a whole table: sort it, filter it to answer questions, and use Filter Views so your sorting doesn't disturb the people you share with.

✅ Before the Next Lesson

Finish the contact-cleanup project and keep it — we reuse cleaning skills in Module 7
Try a TEXT label that mixes words with a number or date
Write your Learning Journal entry for this lesson

📚 Additional Resources

🌟 Encouragement for the Journey

Messy data used to be a chore that ate your afternoon. Now it's a few formulas and a copy-down. That's a genuine professional skill — and you just added it to your toolkit. On to wrangling whole tables. 📊