--- title: 'The 10 Most Useful Airtable Text Formulas' description: 'Master the 10 Airtable text formulas that clean, combine, and reshape data — CONCATENATE, SUBSTITUTE, LEFT, RIGHT, MID, FIND, LEN, TRIM, UPPER, LOWER with examples.' canonical_url: 'https://www.business-automated.com/tutorials/airtable-text-formulas' md_url: 'https://www.business-automated.com/tutorials/airtable-text-formulas.md' last_updated: 2026-04-11 --- Text formulas are the workhorses of every real [Airtable](/airtable-consultant) base. They clean messy imports, combine fields into display-friendly labels, generate slugs and IDs, and turn unstructured data into something you can actually filter and sort on. There are fifteen or so text functions in Airtable's formula library. Ten of them do ninety percent of the work. This guide covers those ten — in the order you'll actually reach for them — with copy-paste examples you can adapt to almost any base. For the full formula reference, see our [Airtable formulas cheat sheet](/tutorials/airtable-formulas-cheat-sheet). For the date-math side of formulas, see our guide to [5 essential Airtable date formulas](/tutorials/airtable-date-formulas). ## 1. CONCATENATE (and the `&` Operator) — Combine Fields Into Strings The single most common text operation: stitching two or more fields into one. Airtable gives you two ways to do it. **Syntax:** ```airtable-formula CONCATENATE(text1, text2, ...) text1 & text2 & ... ``` Both are functionally identical. The `&` operator is shorter and reads more naturally for small cases. **Examples:** ```airtable-formula // Full name {First Name} & ' ' & {Last Name} // Email display label {Full Name} & ' <' & {Email} & '>' // Record header with status {Project Name} & ' — ' & {Status} // Formatted address {Street} & ', ' & {City} & ', ' & {State} & ' ' & {Zip} ``` CONCATENATE shines when you're building something from many pieces — like the last example — because the function call makes the structure visually clearer than a long `&` chain. **Handling blank values in concatenation**\ If any field is blank, Airtable concatenates the blank as an empty string, which can leave you with extra spaces or commas. Wrap optional fields in an IF: ```airtable-formula {First Name} & IF({Middle Name}, ' ' & {Middle Name}, '') & ' ' & {Last Name} ``` ## 2. TRIM — Remove Whitespace Data pasted from spreadsheets or emails almost always comes in with hidden leading and trailing spaces. TRIM fixes that in one step. **Syntax:** ```airtable-formula TRIM(text) ``` **Examples:** ```airtable-formula TRIM({Raw Name}) TRIM({Email}) TRIM({Phone}) ``` TRIM only removes leading and trailing whitespace — not multiple spaces in the middle. For full whitespace normalization, combine it with REGEX_REPLACE: ```airtable-formula TRIM(REGEX_REPLACE({Messy Field}, '\s+', ' ')) ``` This is one of the most-used patterns in any serious base. If you're importing data from an external source, consider applying it to every text field on the way in. ## 3. UPPER and LOWER — Normalize Case Two simple functions for case manipulation. Airtable does **not** have a built-in `PROPER` (title-case) function like Excel or Google Sheets — you have to build title casing manually when you need it. **Syntax:** ```airtable-formula UPPER(text) // EVERYTHING IN CAPS LOWER(text) // everything in lowercase ``` **Examples:** ```airtable-formula // Normalize tags before comparison IF(LOWER(TRIM({Tag})) = 'vip', 'Star Customer', 'Standard') // Force display in uppercase UPPER({Country Code}) // Generate a slug base LOWER({Title}) ``` The UPPER/LOWER functions are also how you force case-insensitive comparisons, since Airtable's `=` operator is case-sensitive by default. **Title-casing a single word**\ For a single-word field, you can fake title case by uppercasing the first letter and lowercasing the rest: ```airtable-formula UPPER(LEFT({Name}, 1)) & LOWER(MID({Name}, 2, LEN({Name}))) ``` For multi-word strings (full names, titles), the cleanest approach is to split on space, title-case each part, and recombine — which is awkward in a pure formula. In practice, most teams either normalize to `UPPER` or `LOWER` for consistency, or do the title-casing upstream in a script action or automation step. ## 4. LEFT, RIGHT, MID — Slice Strings By Position When you need a fixed chunk from the beginning, end, or middle of a string. **Syntax:** ```airtable-formula LEFT(text, count) RIGHT(text, count) MID(text, start_position, count) ``` MID is 1-indexed — position 1 is the first character. **Examples:** ```airtable-formula // First 3 characters (country code from a phone number) LEFT({Phone}, 3) // File extension RIGHT({Filename}, 3) // Middle of a SKU: "PRD-12345-US" → "12345" MID({SKU}, 5, 5) // Preview snippet: first 80 chars of a long description LEFT({Description}, 80) & '...' ``` LEFT and RIGHT are most useful when data has predictable structure. MID is the escape hatch for more flexible slicing — especially when combined with FIND. ## 5. FIND and SEARCH — Locate a Substring When you need to know where something appears inside a string. Once you know the position, LEFT/RIGHT/MID can do the actual slicing. **Syntax:** ```airtable-formula FIND(search_string, text, [start_position]) SEARCH(search_string, text, [start_position]) ``` Both return the 1-indexed position of the first occurrence, or 0 if not found. `FIND` is case-sensitive; `SEARCH` is case-insensitive. **Examples:** ```airtable-formula // Position of the @ in an email FIND('@', {Email}) // Extract the domain of an email MID({Email}, FIND('@', {Email}) + 1, 100) // Extract the username part of an email LEFT({Email}, FIND('@', {Email}) - 1) // Check whether a keyword appears in a note IF(SEARCH('urgent', LOWER({Note})) > 0, '🔴 Urgent', '⚪ Normal') ``` The email-splitting pattern is something you'll use constantly — it's the simplest way to derive domain-level segmentation from a contact list. ## 6. LEN — How Long Is This String? **Syntax:** ```airtable-formula LEN(text) ``` Returns the number of characters. **Examples:** ```airtable-formula // Flag records with suspiciously short names IF(LEN({Name}) < 3, '⚠️ Check', 'OK') // Enforce a character limit on a short description IF(LEN({Summary}) > 280, '❌ Too long (' & LEN({Summary}) & ' chars)', '✅ OK') // Build a "contact completeness" score IF(LEN({Phone}), 1, 0) + IF(LEN({Email}), 1, 0) + IF(LEN({Address}), 1, 0) ``` The last pattern — using LEN as a quick "is this field filled in?" check inside a sum — is the fastest way to build completeness scores for contact records, CRM entries, or onboarding checklists. ## 7. SUBSTITUTE — Replace Exact Substrings SUBSTITUTE is the literal-match cousin of REGEX_REPLACE. Use it when you know the exact string you want to replace. **Syntax:** ```airtable-formula SUBSTITUTE(text, old_text, new_text, [instance_number]) ``` The optional fourth argument lets you replace only the Nth occurrence instead of every match. **Examples:** ```airtable-formula // Strip a prefix SUBSTITUTE({Product Code}, 'PRD-', '') // Convert a dotted version to a slash version SUBSTITUTE({Version}, '.', '/') // Replace only the second hyphen SUBSTITUTE('2026-04-11', '-', '/', 2) // returns '2026-04/11' // Normalize curly quotes to straight quotes SUBSTITUTE(SUBSTITUTE({Text}, '"', '"'), '"', '"') ``` For simple one-off replacements, SUBSTITUTE is cleaner and more performant than reaching for regex. ## 8. REPT — Repeat a String **Syntax:** ```airtable-formula REPT(text, count) ``` Returns the text repeated `count` times. Sounds niche, but it's the standard trick for visual progress bars and star ratings inside formulas. **Examples:** ```airtable-formula // 5-star rating from a 1-5 score REPT('⭐', {Score}) & REPT('☆', 5 - {Score}) // Progress bar from a percentage (0-10 scale) REPT('█', {Progress} / 10) & REPT('░', 10 - {Progress} / 10) ``` These visual hacks make Grid and Gallery views feel much more like a real dashboard without touching Interface Designer. ## 9. VALUE — Convert Text to a Number When a field contains a number formatted as text (common after CSV imports), VALUE turns it into something you can do math on. **Syntax:** ```airtable-formula VALUE(text) ``` **Examples:** ```airtable-formula // Parse a price out of a text field VALUE(REGEX_EXTRACT({Description}, '\d+\.?\d*')) // Sum two fields that arrived as text VALUE({Quantity Text}) * VALUE({Price Text}) ``` Pair VALUE with REGEX_EXTRACT to pull numeric values out of freeform text — a common pattern when you're ingesting order notes, quoted prices, or form submissions where fields weren't validated upstream. ## 10. T and ENCODE_URL_COMPONENT — The Niche Essentials Two smaller functions that round out the text toolkit. **T(value)** returns the value if it's text, or blank if it's a number. Useful when you want to guard against mixed-type fields. **ENCODE_URL_COMPONENT(text)** is the secret weapon for building dynamic URLs — it percent-encodes a string so you can safely embed it in a link. ```airtable-formula 'https://example.com/search?q=' & ENCODE_URL_COMPONENT({Query}) // Pre-fill a form URL with record data 'https://airtable.com/shrXXXX?prefill_Email=' & ENCODE_URL_COMPONENT({Email}) ``` This is how you build click-through URLs to external tools, prefilled Airtable forms, and Google Maps links — without your links breaking when someone types a space or ampersand. ## Real-World Combinations The ten functions above are lego bricks. The real value comes from combining them. **Slug generator** ```airtable-formula LOWER(REGEX_REPLACE(TRIM({Title}), '\s+', '-')) ``` Trim the edges, collapse whitespace into hyphens, lowercase the whole thing. Instant URL-friendly slug for any title. **Formatted contact card** ```airtable-formula {First Name} & ' ' & {Last Name} & '\n' & UPPER({Company}) & '\n' & {Email} & ' · ' & {Phone} ``` Clean display of name, company, and contact details — perfect for Interface Designer detail panels. **Invoice number from record data** ```airtable-formula 'INV-' & DATETIME_FORMAT(TODAY(), 'YYYYMM') & '-' & RIGHT('0000' & RECORD_ID(), 4) ``` Builds a month-prefixed, zero-padded invoice number like `INV-202604-a1B2`. RECORD_ID gives you the unique suffix; RIGHT pads it out. **Email domain segmentation** ```airtable-formula MID({Email}, FIND('@', {Email}) + 1, LEN({Email})) ``` Extracts the part of an email after the `@`. Drop this into a formula field on your Contacts table and you can instantly group or filter by company domain. ## Performance and Sanity Notes A few things to keep in mind as your text formulas grow: - **Formulas that reference other formula fields are fine**, but circular references aren't allowed. - **Very long text fields** (tens of thousands of characters) can slow things down if you run regex or MID across the whole value. Use LEFT to cap the input first. - **Always wrap user-entered fields in TRIM** at minimum when you're comparing values. It's the cheapest bug prevention you can do. - **Remember that Airtable's `=` is case-sensitive**. Normalize with LOWER or UPPER when comparing user input. ## Bigger Picture Text formulas are boring until they aren't. When a CRM needs a clean "display name" field, when a marketing team wants automatic campaign slugs, when an operations team needs to derive a "domain type" from contact emails — these ten functions are what make those features one-line changes instead of scripting projects. If you're serious about learning Airtable formulas cold, pair this guide with our [SWITCH-to-escape-IF-hell tutorial](/tutorials/airtable-switch-vs-nested-if) and our [complete IF statements guide](/tutorials/airtable-if-statements-complete-guide). Between them you'll have the text and logic foundations to write almost any formula you need. ## Need a Base Rescued? If you've inherited a base where half the text fields are inconsistent — mixed case, stray whitespace, malformed emails, inconsistent naming — that's exactly the sort of cleanup work we do at [Business Automated](/airtable-consultant). We audit, normalize, and document Airtable text data so your CRM, your reporting, and your automations stop throwing surprises. Get in touch and we'll show you what clean looks like. ## Sitemap See the full [sitemap](/sitemap.md) for all pages.