Text manipulation
Split, join, extract, replace, clean — every text-handling pattern from name splitting to building URL slugs to normalizing phone numbers.
57 pages · 57 how-to
How-to guides
How to split text into columns
Split a full name or any delimited text into separate columns. Google Sheets uses SPLIT; Excel uses TEXTSPLIT — here is each.
Excel ≠ SheetsHow to remove extra spaces
Clean leading, trailing, and double spaces with TRIM — the usual fix for lookups that fail on values that look identical.
How to convert text to a number
Turn numbers stored as text into real numbers with VALUE or by multiplying by 1 — the fix for sums that ignore values.
How to extract the domain from an email address
Pull everything after the @ from an email using FIND and MID. Works the same in Excel and Google Sheets.
How to extract the first name from a full name
Get the first name from a full name with LEFT and FIND, splitting on the first space. Works in Excel and Google Sheets.
How to add leading zeros
Pad numbers to a fixed width with leading zeros using TEXT, or a custom number format. Works in Excel and Google Sheets.
How to remove specific characters
Strip out a character — like dashes or currency symbols — with SUBSTITUTE. Nest it to remove several. Works in both apps.
How to find the position of a character
Locate where a character appears in text using FIND — the basis for splitting codes or extracting parts of a string.
How to pad numbers with leading zeros
Add leading zeros to numbers to a fixed width using RIGHT and concatenation — great for IDs and codes. Same in both apps.
How to split text into columns
Use LEFT, MID, RIGHT with FIND/SEARCH to split delimited text into separate columns with formulas.
How to split first and last name
Extract first and last names from a single cell using LEFT, MID, FIND, and LEN formulas.
How to extract the last name
Pull the last name from a "First Last" or "Last, First" formatted cell using MID, FIND, and LEN.
How to extract numbers from a text string
Pull numeric digits out of mixed text strings using array formulas, VALUE, and MID combinations.
Excel ≠ SheetsHow to extract text from a string
Use LEFT, RIGHT, MID, and FIND to pull specific substrings from any position within a cell.
How to remove the first character from a cell
Drop the first character of a string with RIGHT and LEN — a simple two-function formula.
How to remove the last N characters from a cell
Strip trailing characters from a string using LEFT and LEN — one clean formula handles any count.
How to remove non-numeric characters
Strip letters and symbols from a cell to leave only digits using array formulas or REGEXREPLACE.
Excel ≠ SheetsHow to remove line breaks from a cell
Replace CHAR(10) line breaks in a cell with a space or nothing using SUBSTITUTE or CLEAN.
How to remove leading zeros
Convert text-stored numbers with leading zeros to true numeric values using VALUE or multiplication.
How to capitalize the first letter
Uppercase the first character of a string while leaving the rest unchanged using UPPER, LEFT, and MID.
How to convert text to uppercase
Convert any text string to ALL CAPS instantly using the UPPER function in Excel and Google Sheets.
How to convert text to lowercase
Convert any text string to all-lowercase using the LOWER function in Excel and Google Sheets.
How to convert text to proper case
Title-case a text string so each word starts with a capital using the PROPER function.
How to reverse a text string
Reverse the order of characters in a string using a TEXTJOIN array formula or MID with SEQUENCE.
Excel ≠ SheetsHow to find and replace text with a formula
Use SUBSTITUTE to programmatically find and replace text within a cell without using the Find & Replace dialog.
How to replace multiple values at once
Chain SUBSTITUTE calls or use REDUCE/LAMBDA to replace many different strings in one formula.
How to combine text from two cells
Join two cell values into one using the & operator or CONCAT/CONCATENATE functions.
How to combine text with a delimiter
Join multiple cells with a comma, dash, or any separator using TEXTJOIN or the & operator.
How to get the text before a character
Extract everything to the left of a delimiter using LEFT and FIND, or the new TEXTBEFORE function.
How to get the text after a character
Extract everything to the right of a delimiter using MID and FIND, or the modern TEXTAFTER function.
How to get the text between two characters
Extract a substring between two delimiter characters using MID and FIND, or REGEXEXTRACT in Sheets.
Excel ≠ SheetsHow to generate initials from a name
Extract the first letter of each word in a full name to build initials using LEFT, MID, FIND, and related text functions.
How to count occurrences of a word in a cell
Use LEN and SUBSTITUTE to count how many times a specific word appears inside a single cell in Excel and Google Sheets.
How to convert text to a date
Use DATEVALUE to parse a text string that looks like a date into a real date serial number in Excel and Google Sheets.
How to extract the state from an address
Pull a US state abbreviation from a full address string using MID and FIND to locate commas and spaces.
How to extract a zip code from an address
Extract a 5-digit US ZIP code from the end of an address string using RIGHT and TRIM, or a regex in Sheets.
Excel ≠ SheetsHow to format a phone number
Format a 10-digit number string as (###) ###-#### using TEXT, or combine LEFT, MID, and RIGHT for more control.
How to mask part of a string
Hide sensitive characters in a string by replacing middle characters with asterisks using LEFT, RIGHT, and REPT.
How to get the nth word from a sentence
Extract the Nth word from a text string using TRIM, MID, and FIND with SUBSTITUTE to locate word boundaries.
How to get the last word in a cell
Extract the last word from a text string using TRIM, RIGHT, SUBSTITUTE, and LEN to locate the final space.
How to get the first word in a cell
Extract the first word from a text string using LEFT and FIND to locate the first space.
How to swap first and last name
Rearrange 'First Last' to 'Last, First' using FIND, LEFT, MID, and LEN to split on the space.
How to remove everything after a character
Strip all text after a delimiter character using LEFT and FIND to keep only the portion before the character.
How to remove everything before a character
Extract only the text after a delimiter using MID and FIND to skip everything up to and including the character.
How to extract a file extension from a filename
Pull the file extension from a filename string using RIGHT and FIND on the last period in the name.
How to extract the username from an email
Get the username (part before @) from an email address using LEFT and FIND.
How to count the lines in a cell
Count the number of lines in a multi-line cell by counting line break characters with LEN and SUBSTITUTE.
How to repeat a character N times
Use the REPT function to repeat any character or string a specified number of times in a cell.
How to create a URL slug from text
Convert a title into a lowercase, hyphenated URL slug using LOWER and SUBSTITUTE to replace spaces.
How to remove a currency symbol from a number
Strip currency symbols like $ or £ from text-formatted numbers using SUBSTITUTE or VALUE with TRIM.
How to add a prefix to every cell in a column
Prepend a fixed string to every cell in a column by concatenating the prefix with the cell reference using & or CONCAT.
How to add a suffix to every cell in a column
Append a fixed string to every cell in a column by concatenating the cell reference with the suffix using &.
How to extract every number from a text string
Extract all numbers embedded in a text string in Google Sheets with REGEXEXTRACT, or use a TEXTSPLIT/TRIM approach in Excel 365, to pull every numeric value from mixed text.
Excel ≠ SheetsHow to parse a CSV string inside a single cell
Split a comma-separated string stored in a single cell into individual values using TEXTSPLIT in Excel 365 or SPLIT in Google Sheets, spilling each value into its own cell.
Excel ≠ SheetsHow to extract hashtags from a text string
Pull every #hashtag from a social media post or text cell in Google Sheets with REGEXEXTRACT, or use TEXTSPLIT and FILTER in Excel 365 to isolate tokens that start with #.
Excel ≠ SheetsHow to normalize phone numbers to a single format
Strip formatting characters from phone numbers and reformat them to a consistent pattern in Excel and Google Sheets using SUBSTITUTE and TEXT, or REGEXREPLACE in Sheets.
Excel ≠ SheetsHow to extract a currency amount from a text string
Extract a numeric currency amount from a text string like "Total: $1,234.56 due" in Google Sheets with REGEXEXTRACT, or use SUBSTITUTE and VALUE in Excel for a numeric result.
Excel ≠ Sheets