The idea
Exports never arrive in the shape a report needs. The email holds the company, but glued to a name. The order date holds the month, but as a full timestamp. This chapter has two ideas that fix most of it.
First, text is a row of characters with positions. Functions such as LEFT, MID, RIGHT, LEN, FIND and SUBSTITUTE cut and measure that row. Second, a date is not text at all: it is a number, and everything you do with dates is arithmetic on that number.
Figure 1
Splitting owen.hudson@icloud.com at the @
-
FIND("@",D2)position of the @ : 12 -
LEFT(D2, 12-1)the 11 characters before it -
RIGHT(D2, LEN(D2)-12)the 10 characters after it
- A customer email of 22 characters sits in
D2:owen.hudson@icloud.com. Every character has a position, counted from 1. FIND("@", D2)returns the position of the first @: 12. The address is a different length for every customer, so you find the cut instead of typing it.LEFTkeeps characters from the start. Ask for one fewer than the @ position: 11 characters,owen.hudson.RIGHTkeeps characters from the end.LEN(D2)is 22, minus 12 leaves 10:icloud.com.
Why it exists
A person reads owen.hudson@icloud.com as a name and a company and sees the boundary at a glance. A formula has no eyes. It needs a rule it can repeat for ten thousand rows, and the rule is positional: "find the @, keep what is before it". Once you describe the cut as a position, the same formula works for a short address and for a long one.
Dates have the opposite problem. People see "28 Nov 2025" and think of text. If dates were text, "30 days later" would need a calendar lookup. Because they are numbers, a deadline is a plus sign, and the days between an order and a return is a minus sign. A simple trick underneath makes calendars computable.
How it works under the hood
Position is everything in text
Positions start at 1. LEFT(text, n) and RIGHT(text, n) keep n characters from either end. MID(text, start, n) keeps n characters beginning at a position. LEN counts every character, spaces included. FIND(find_text, within_text) returns the position of the first match and is case-sensitive; SEARCH is the same but ignores case and accepts wildcards. If the text is not found, both return #VALUE!, which is how a row without an @ shows itself.
The pattern for any "split at a marker" job is the same three steps: locate the marker with FIND, do the arithmetic to get the number of characters you want (one less than the position for "before", LEN minus the position for "after"), and hand that number to LEFT, RIGHT or MID. Hardcoding a number such as 11 works for one row and breaks on the next.
When the data has one clean delimiter and you only need to split it once, Text to Columns (Data menu) is quicker and needs no formula. Reach for formulas when the split must keep working on new rows.
Gluing text back together
The & operator joins pieces: =A2&" | "&UPPER(B2). It converts numbers to text on the way, with the default format. To control the format, wrap the value in TEXT(value, "pattern"), for example TEXT(date,"yyyy-mm-dd") for the date part of a label or TEXT(price,"0.00") for two decimals. Patterns such as yyyy, mm, dd and 0.00 are format codes, and they must be written inside quotes.
Nested functions evaluate inside out
A formula such as =SUBSTITUTE(LOWER(D2)," ","-") is a pipeline written inside out. The innermost call runs first and its result is handed outwards as an argument.
Figure 2
Nested functions evaluate from the inside out
-
D2Pine Cast Iron Carafe -
LOWER(D2)pine cast iron carafe -
SUBSTITUTE(…, " ", "-")pine-cast-iron-carafe
- A URL slug for product 143:
=SUBSTITUTE(LOWER(D2), " ", "-"). Excel starts with the innermost call, the one with no function inside it. LOWER(D2)runs first and returnspine cast iron carafe.- That result becomes the first argument of
SUBSTITUTE, which swaps each space for a hyphen:pine-cast-iron-carafe.
To read a nested formula, start at the deepest parenthesis and work out. To write one, build the first step in its own cell, check it, then wrap it in the next function. The formula bar's colour-coded brackets help you match them up.
A date is a number with a format
The serial number counts days from 1 January 1900, with times as the fraction of a day. Cell formats decide whether a cell shows 45989 or 28/11/2025. That is the answer to the classic puzzle "why does my date show as 45672?": the format is General, so you see the number underneath. Set the cell to a date format and the same value shows as a date.
Figure 3
A date is a number: arithmetic on order 12059's date
| cell | formula | stored_value | displayed_as |
|---|---|---|---|
| A2 | 2025-11-28 | 45989 | 2025-11-28 |
| B2 | =A2+30 | 46019 | 2025-12-28 |
| C2 | =B2-A2 | 30 | 30 |
| D2 | =TEXT(A2,"yyyy-mm") | 2025-11 | 2025-11 |
- Excel stores a date as the number of days since 1 January 1900. 28 November 2025 is 45989. The date you see is that number wearing a date format.
- Adding 30 to a date is just adding 30 to a number: 46019, which the format displays as 28 December 2025. This is how a return deadline is built.
- Subtract one date from another and the answer is the number of days between them: 30. Format the cell as a number, not a date, or you will see 30 January 1900.
TEXTconverts the number into a string with the pattern you give it.D2is now text,2025-11, good for a month key and not for arithmetic.
Because a date is a number, a deadline is date + days, the span between two dates is a subtraction, and the first of the next month is a calculation you can write. EDATE(date, months) adds whole months properly, so an anniversary stays on the same day of the month. WEEKNUM converts a date into a week number, useful for same-week checks. DATEDIF(start, end, "d") returns whole days, months or years between two dates; Excel does not list it in the function wizard, but it works.
Where dates go wrong
A "date" imported from a CSV is sometimes text. It looks right, left-aligned, but cannot be added to. If ISNUMBER returns FALSE for it, convert it first (the cleaning chapter shows how). Also, dates written as 03/04/2025 are ambiguous: day-first or month-first depends on the regional setting of the computer that opens the file. Store dates as real dates and format them for display.
Common misconceptions
"A date in a cell is text that looks like a date"
A real date is a number. You can tell by the right alignment, and by changing the cell's format to Number and watching 45989 appear. A text date left-aligned in a cell is the imposter, and it will not sort, subtract or filter by month correctly.
"TEXT just changes how a cell looks"
Formatting a cell changes how it looks and keeps the number. TEXT returns a different object, a string. Use it to build labels and month keys, never on a value you still want to calculate with.
"FIND and SEARCH are the same"
FIND is case-sensitive and takes no wildcards. SEARCH ignores case and accepts ? and *. For a keyword check such as "does the name contain this word", SEARCH is usually what you want, wrapped in ISNUMBER so that a missing word gives FALSE and not an error.
Check yourself
One question. Answering it ticks this lesson off on the map; it never locks a level.
Not quite. TEXT produces a string and throws the number away. Keep the original date in A2 for arithmetic.Try another answer.
Right. TEXT returns text, and a label like 2025-11 cannot be added to. The arithmetic belongs on the date in A2; the label is only for grouping and display.
Not quite. Excel does not evaluate the content of a text string as a formula.Try another answer.
Not quite. A text label has no days in it. Excel cannot add a number of days to it.Try another answer.
Lesson done: it is ticked on the map.