Split, join, clean, search and reshape text — names, codes, addresses and imported data. This page lists all 52 of them; each links to a full lesson with an example you can copy and a practice file.
ARRAYTOTEXTARRAYTOTEXT turns a range into a single text string. Strict mode gives array-constant syntax you can paste into…ASCASC converts full-width (double-byte) letters and katakana to half-width. In English text there is nothing to convert, so…BAHTTEXTBAHTTEXT writes a number as Thai text with 'Baht'. There is no rupee equivalent built in; for Indian…CHARCHAR returns the character for a code number. CHAR(10) is a line break — the key to multi-line…CLEANCLEAN removes invisible control characters (like line breaks from system exports) that break lookups and look like spaces.CODECODE returns the number of the first character. It is the detective tool for “invisible” characters that break…CONCATCONCAT joins text from several cells or whole ranges. It replaces the older CONCATENATE. The & operator does…CONCATENATECONCATENATE joins text pieces. It still works for compatibility, but it cannot take a range — CONCAT and…DBCSDBCS is the older name of JIS, converting to full-width characters.DETECTLANGUAGEDETECTLANGUAGE returns the language code of a text — useful for sorting customer feedback before translating it.DOLLARDOLLAR formats a number as currency text using your computer’s currency symbol. The name is historic — on…ENCODEURLENCODEURL converts spaces and symbols into URL codes (%20 and so on).EXACTEXACT compares two texts including upper/lower case. A normal =A2=B2 ignores case.FILTERXMLFILTERXML extracts values from XML with an XPath. Its famous side use: splitting text in versions without TEXTSPLIT.FINDFIND returns where one text starts inside another. It is case-sensitive and does not allow wildcards — use…FINDBFINDB is the byte-based version of FIND.FIXEDFIXED rounds a number and returns it as text with or without thousands separators — good for sentences…JISJIS converts half-width characters to full-width (double-byte), mainly for Japanese documents. It is available only when Japanese is…LEFTLEFT returns the first characters of a text. RIGHT takes them from the end.LEFTBLEFTB returns the first characters based on bytes. In English (single-byte) setups it behaves exactly like LEFT; it…LENLEN returns the number of characters, including spaces.LENBLENB returns the number of bytes; identical to LEN in English setups, double for East Asian characters when…LOWERLOWER converts all letters to lowercase — handy when comparing emails or usernames typed inconsistently.MIDMID returns characters from the middle of a text, starting at a position you choose.MIDBMIDB is the byte-based version of MID; identical to MID in English setups.NUMBERVALUENUMBERVALUE converts text to a number using separators you specify — essential for European-style “1.234,56” exports.PHONETICPHONETIC returns the phonetic (furigana) characters stored with Japanese text typed on a Japanese input method. Text without…PROPERPROPER capitalises the first letter of each word; UPPER and LOWER change the whole text.REGEXEXTRACTREGEXEXTRACT returns the part of text that matches a pattern — invoice numbers, phone numbers, amounts buried in…REGEXREPLACEREGEXREPLACE replaces text matching a pattern — strip every non-digit, collapse repeated spaces, mask numbers.REGEXTESTREGEXTEST returns TRUE if text matches a regular expression — validate PAN numbers, emails or codes in one…REPLACEREPLACE swaps characters at a given position — useful for masking.REPLACEBREPLACEB is the byte-based version of REPLACE.REPTREPT repeats text a number of times — best known for quick bar charts inside cells.RIGHTRIGHT returns the last characters of a text value.RIGHTBRIGHTB is the byte-based version of RIGHT; identical to RIGHT in English setups.SEARCHSEARCH works like FIND but ignores case and accepts wildcards (? and *). With ISNUMBER it becomes a…SEARCHBSEARCHB is the byte-based version of SEARCH.SUBSTITUTESUBSTITUTE replaces existing text with new text — all occurrences or just the n-th one.TT returns text unchanged and anything else as an empty string. Rarely needed alone, but tidy for mixed…TEXTTEXT converts a number or date into text using a format code — needed when you join values…TEXTAFTERTEXTAFTER returns the text that follows a delimiter — domains from emails, surnames, file extensions.TEXTBEFORETEXTBEFORE returns everything before a delimiter; TEXTAFTER everything after. They replace most LEFT/FIND and MID/FIND combinations.TEXTJOINTEXTJOIN joins a range of text with a delimiter and can ignore empty cells.TEXTSPLITTEXTSPLIT splits text by a delimiter into columns and/or rows — a formula version of Text to Columns…TRANSLATETRANSLATE uses Microsoft’s translation service to translate text between languages, right in the sheet.TRIMTRIM removes leading and trailing spaces and reduces multiple spaces between words to one. Essential after copying data…UNICHARUNICHAR returns a character from its Unicode number — arrows, ticks, the rupee sign — without hunting through…UNICODEUNICODE returns the code point of the first character. Unlike CODE it is correct for Hindi, emoji and…UPPERUPPER makes text all capitals; LOWER makes it all small letters. Useful for codes, PAN and GSTIN that…VALUEVALUE turns text that looks like a number into a real number, so SUM and lookups work.VALUETOTEXTVALUETOTEXT converts any value to text; strict mode adds quotes around text so you can see stray spaces.