Building text: TEXT, SUBSTITUTE, TEXTJOIN and a first regex

The first text lecture was about cleaning. This one is about building text: a headline for a dashboard, a tidy UTM code, a list you can paste into an email.

TEXT(value, format) turns a number or date into text that looks the way you choose. You need it whenever a number goes into a sentence, because & drops the formatting. ="Revenue: "&B2 shows Revenue: 48250. ="Revenue: "&TEXT(B2, "$#,##0") shows Revenue: $48,250. Formats worth knowing:

• "$#,##0" for dollars, "0.0%" for a percent with one decimal.
• "mmmm yyyy" gives March 2026, "mmm d" gives Mar 9, "ddd" gives Mon.
• "yyyy-mm" gives 2026-03, a month key that sorts in the right order.

The result is text, so don't do math on it. Keep the number in its own cell and use TEXT for display.

SUBSTITUTE(text, old, new) swaps every copy of one piece of text for another: spaces for dashes in a URL, "Inc." for nothing. To swap two things, nest it. The inner SUBSTITUTE runs first.

TEXTJOIN(delimiter, ignore_empty, range) is SPLIT in reverse. It glues a range into one cell with a separator between the pieces. Set the second input to TRUE so blank cells don't leave you with a, , b.

REGEXMATCH(text, pattern) returns TRUE when the text contains the pattern. A regular expression is a small search language, and three pieces cover most everyday checks:

• | means or. "gmail|yahoo" matches either one.
• ^ means starts with and $ means ends with. "^SKU-" checks a prefix.
• [0-9]+ means one or more digits, so "[0-9]+" finds any number in the text.

REGEXMATCH is case-sensitive, so wrap the text in LOWER if you're not sure how it was typed. Put it inside IF to turn TRUE and FALSE into labels people can read.