Texts

Join, cut, search, replace, clean and format texts; read a field from a long text; regular expressions.

Texts come from Text, Long text, Status, Dropdown, Email, Phone and most other columns. A text written in the formula goes between double quotes: "Done".

Joining texts

You write	Gives
`{item's First name} & " " & {item's Last name}`	`Mario Rossi`
`CONCAT({item's Code}, "-", {item's Year})`	`PRJ-2026` (`CONCATENATE` works too)
`TEXTJOIN(", ", TRUE, {item's City}, {item's Country})`	The texts with a separator, empty ones skipped
`"Total: " & TEXT({item's Amount}, "#,##0.00")`	`Total: 1,234.50`

Cutting and measuring

Function	Gives	Example
`LEFT(text, n)` / `RIGHT(text, n)`	The first / last n characters	`LEFT("INV-2026-031", 3)` → `INV`
`MID(text, start, n)`	n characters from position start (1 is the first)	`MID("INV-2026-031", 5, 4)` → `2026`
`LEN(text)`	How many characters	`LEN("Padova")` → 6
`TEXT.BEFORE(text, delimiter, [n])`	The text before the delimiter (the n-th one; negative from the end) new recipes	`TEXT.BEFORE("[email protected]", "@")` → `mario`
`TEXT.AFTER(text, delimiter, [n])`	The text after it new recipes	`TEXT.AFTER("[email protected]", "@")` → `acme.com`
`TEXT.WORDS(text)` / `TEXT.LINES(text)`	The words / the lines, as a list new recipes	`LIST.COUNT(TEXT.WORDS({item's Notes}))` → number of words
`TEXT.INITIALS(text)`	The first letter of each word, in capitals new recipes	`TEXT.INITIALS("Mario De Rossi")` → `MDR`

TEXT.BEFORE and TEXT.AFTER ignore upper and lower case and give an empty text when the delimiter isn’t there. The Excel versions TEXTBEFORE and TEXTAFTER work too.

Searching and replacing

Function	Gives	Example
`FIND(what, text)`	The position of what, case-sensitive; `#VALUE!` if missing	`FIND("-", "INV-031")` → 4
`SEARCH(what, text)`	The same, ignoring case; `*` and `?` work as wildcards	`ISNUMBER(SEARCH("urgent", {item's Subject}))` → TRUE if it contains “urgent”
`SUBSTITUTE(text, old, new, [n])`	Every old replaced by new (or only the n-th)	`SUBSTITUTE({item's Phone}, " ", "")`
`REPLACE(text, start, n, new)`	n characters from start replaced	`REPLACE("2026-031", 1, 4, "2027")` → `2027-031`
`EXACT(a, b)`	TRUE if identical, upper and lower case included	`EXACT({item's Code}, "ABC")`

Cleaning and changing case

Function	Gives
`TRIM(text)`	The text without spaces at the ends and with single spaces inside
`UPPER(text)` / `LOWER(text)` / `PROPER(text)`	`MARIO ROSSI` / `mario rossi` / `Mario Rossi`
`REPT(text, n)`	The text repeated n times: `REPT("★", {item's Rating})`
`CHAR(10)`	A line break, to join texts on separate lines in a Long text column

Reading a field from a long text

Emails, forms and imports often put several fields in one Long text column:

Customer: ACME Srl
VAT: IT01234567890
City: Padova

TEXT.FIELD(text, label) gives the value after a label, ignoring upper and lower case and the colon new recipes:

=TEXT.FIELD({item's Request}, "VAT")          → IT01234567890

Numbers and dates as text

Function	Gives	Example
`TEXT(value, format)`	A number or date written with a format	`TEXT(1234.5, "#,##0.00")` → `1,234.50`, `TEXT({item's Date}, "DD/MM/YYYY")`
`FIXED(number, [decimals], [no commas])`	A number with fixed decimals	`FIXED(3.14159, 2)` → `3.14`
`VALUE(text)`	The number in a text	`VALUE("42")` → 42

For dates, DATE.FORMAT also writes month and day names in your language: see Dates.

Regular expressions new recipes

Regular expressions find text by its pattern: an email address, an order number, a VAT number.

Function	Gives	Example
`REGEXEXTRACT(text, pattern)`	The first part that matches, or the part in brackets; `#N/A` if nothing matches	`REGEXEXTRACT({item's Subject}, "Order #(\d+)")` → `4521`
`REGEXREPLACE(text, pattern, replacement)`	Every match replaced; `$1`, `$2` are the parts in brackets	`REGEXREPLACE({item's Name}, "(\w+) (\w+)", "$2 $1")` → `Rossi Mario`
`REGEXMATCH(text, pattern)`	TRUE if the text matches	`REGEXMATCH({item's VAT}, "^IT\d{11}$")`

=REGEXEXTRACT({item's Email body}, "[\w.+-]+@[\w-]+\.[\w.]+")      → the first email address in the text

A pattern that isn’t valid gives #VALUE!. Patterns are limited to 500 characters and texts to 50,000.

Step by step new recipes

Every text function can follow the text with a dot:

={item's Request}.FIELD("Customer").TRIM().UPPER()
={item's Name}.INITIALS()

A function given a list works on each of its items: UPPER(LIST.SPLIT({item's Tags})) gives every tag in capitals.