Preparing your learning space...
83% through AI Formula Generator tutorials
Text functions cut, clean, join, and reshape text. They're the tools of data cleaning — splitting full names, stripping stray spaces, standardizing case, and pulling IDs out of messy strings — so the rest of your formulas get clean input.
These three pull characters out of a string. LEFT takes from the beginning, RIGHT from the end, and MID from anywhere in the middle.
Syntax: =LEFT(text, [num_chars]) — =RIGHT(text, [num_chars]) — =MID(text, start_num, num_chars)
=LEFT(A2, 3) =RIGHT(A2, 4) =MID(A2, 2, 5)
LEFT(A2, 3) returns the first 3 characters — an area code or a prefix. RIGHT(A2, 4) returns the last 4 — often a year or a code suffix. MID(A2, 2, 5) starts at character 2 and takes 5 characters from there. MID is the flexible one; LEFT and RIGHT are just its two special cases.
LEN counts how many characters a cell contains. TRIM removes the extra spaces around and between words. They're the first thing you reach for when imported data looks wrong.
=LEN(A2) =TRIM(A2) =LEN(TRIM(A2))
LEN(A2) gives a character count — handy for spotting IDs of the wrong length. TRIM(A2) collapses " John Smith " into "John Smith", removing leading, trailing, and double spaces. LEN(TRIM(A2)) shows the true length after the spaces are gone.
Best practice: If lookups on text keep failing, suspect invisible spaces. TRIM them before comparing — a space at the end is the classic reason =VLOOKUP(...) suddenly returns #N/A.
These join text together. CONCATENATE is the older name, CONCAT is its modern replacement, and TEXTJOIN adds a separator and can skip blank cells.
Syntax: =CONCATENATE(text1, [text2], ...) — =TEXTJOIN(delimiter, ignore_empty, text1, ...)
=CONCATENATE(A2, " ", B2) =CONCAT(A2:B2) =TEXTJOIN(" - ", TRUE, A2:C2)
The first builds a full name from first name (A2) and last name (B2) with a space between. CONCAT(A2:B2) joins a whole range at once. TEXTJOIN(" - ", TRUE, A2:C2) joins three cells separated by " - ", and TRUE tells it to skip any blanks — so a missing middle cell doesn't leave double separators.
These standardize case. UPPER makes everything uppercase, LOWER makes it lowercase, and PROPER capitalizes the first letter of each word.
=UPPER(A2) =LOWER(A2) =PROPER(A2)
UPPER(A2) turns "john smith" into "JOHN SMITH". PROPER(A2) turns "john SMITH" into "John Smith" — the quick fix when a mailing list arrived with mixed caps.
Both swap part of a string, but they decide what to change differently. SUBSTITUTE replaces specific text wherever it appears; REPLACE replaces characters by position.
Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num]) — =REPLACE(old_text, start_num, num_chars, new_text)
=SUBSTITUTE(A2, "-", "") =REPLACE(A2, 3, 2, "XX")
SUBSTITUTE(A2, "-", "") removes every dash — cleaning an ID like "AB-12-CD" into "AB12CD". REPLACE(A2, 3, 2, "XX") overwrites the two characters starting at position 3 with "XX", no matter what those characters are.
TEXT changes how a number or date displays by converting it to text with a specific format. Use it when you need "1,234.50" or "05-Aug-2026" as a string, not just on screen.
Syntax: =TEXT(value, format_text)
=TEXT(A2, "0.00") =TEXT(B2, "dd-mmm-yyyy")
TEXT(A2, "0.00") formats 1234.5 as "1234.50". TEXT(B2, "dd-mmm-yyyy") turns a date serial into "05-Aug-2026". The result is text, so you can concatenate it — but it's no longer a number, so you can't do math on it.
Text formulas get tedious when you're cutting strings by position. Both Microsoft Copilot (in Excel 365) and ChatGPT can write them if you describe the input and the shape you want out.
In Excel with Microsoft Copilot — open the Copilot chat pane and describe the transformation:
With ChatGPT — show a sample value and the target:
Tip: Give the AI a concrete example value — "A2 is AB-12-CD, I want AB12CD" — so it picks SUBSTITUTE vs MID correctly instead of guessing.
Save your progress and earn XP for completing tutorials.
Technology
Excel with AI
Lesson group
AI Formula Generator
Progress
83% complete
