Text formulas
Split, join, extract and clean text: names, emails, codes and messy imports. 98 answers, each checked by running it, with a live example and an exercise.
How to extract the first name from a full name in Excel
=TEXTBEFORE(A2," ")In Excel 365 use =TEXTBEFORE(A2," "). In any version, =LEFT(A2,FIND(" ",A2)-1) takes everything before the first space.
How to extract text after a character in Excel
=TEXTAFTER(A2,"@")Use =TEXTAFTER(A2,"@") in Excel 365. In any version, =MID(A2,FIND("@",A2)+1,LEN(A2)) returns everything after the character.
How to combine text from two cells in Excel
=A2&" "&B2Join them with & and put a space in quotes between: =A2&" "&B2. TEXTJOIN joins whole ranges with a separator.
How to remove extra spaces from text in Excel
=TRIM(A2)Use =TRIM(A2). It removes spaces at the start and end and turns runs of spaces between words into one.
How to capitalize the first letter of each word in Excel
=PROPER(A2)Use =PROPER(A2). It capitalizes the first letter of every word and lowercases the rest.
How to count characters in a cell in Excel
=LEN(A2)Use =LEN(A2). It counts every character, including spaces and punctuation.
How to extract the last name from a full name in Excel
=TEXTAFTER(A2," ",-1)In Excel 365 use =TEXTAFTER(A2," ",-1), which returns everything after the last space. In older versions use =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)).
How to extract the middle name from a full name in Excel
=TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,,,"")Take the text between the first and the last space: =TEXTBEFORE(TEXTAFTER(A2," ")," ",-1,,,""). Names without a middle name return blank.
How to get the initials of a full name in Excel
=UPPER(CONCAT(LEFT(TEXTSPLIT(A2," "),1)))In Excel 365, split the name into words and keep each first letter: =UPPER(CONCAT(LEFT(TEXTSPLIT(A2," "),1))).
How to change Last, First to First Last in Excel
=TRIM(TEXTAFTER(A2,","))&" "&TEXTBEFORE(A2,",")Put the part after the comma first: =TRIM(TEXTAFTER(A2,","))&" "&TEXTBEFORE(A2,","). In older versions use MID and LEFT with FIND.
How to switch first and last names with a comma in Excel
=TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1)Split at the last space and swap the parts: =TEXTAFTER(A2," ",-1)&", "&TEXTBEFORE(A2," ",-1) turns "Mary Ann Smith" into "Smith, Mary Ann".
How to remove the middle name from a full name in Excel
=TEXTBEFORE(A2," ")&" "&TEXTAFTER(A2," ",-1)Join the first word and the last word: =TEXTBEFORE(A2," ")&" "&TEXTAFTER(A2," ",-1). Names with no middle name come back unchanged.
How to display first name and last initial in Excel
=TEXTBEFORE(A2," ")&" "&LEFT(TEXTAFTER(A2," ",-1),1)&"."Join the first word to the first letter of the last word: =TEXTBEFORE(A2," ")&" "&LEFT(TEXTAFTER(A2," ",-1),1)&"." gives "Maria L.".
How to create email addresses from first and last names in Excel
=LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@"&$F$1Join the names with a period, remove spaces and apostrophes, lowercase it and add the domain: =LOWER(SUBSTITUTE(SUBSTITUTE(A2&"."&B2," ",""),"'",""))&"@"&$F$1.
How to create a username from first initial and last name in Excel
=LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'",""))Take the first letter of the first name and the whole last name, in lowercase: =LOWER(LEFT(A2,1)&SUBSTITUTE(B2,"'","")).
How to extract the username from an email address in Excel
=TEXTBEFORE(A2,"@")Take the text before the @: =TEXTBEFORE(A2,"@") in Excel 365, or =LEFT(A2,FIND("@",A2)-1) in any version.
How to extract the domain from a URL in Excel
=SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A2,"//",,,,A2)&"/","/"),"www.","")Drop everything up to // and everything from the next /, then remove www.: =SUBSTITUTE(TEXTBEFORE(TEXTAFTER(A2,"//",,,,A2)&"/","/"),"www.","").
How to extract the file name from a file path in Excel
=TEXTAFTER(A2,"\",-1)Take the text after the last backslash: =TEXTAFTER(A2,"\",-1) in Excel 365.
How to extract the file extension from a file name in Excel
=TEXTAFTER(A2,".",-1,,,"")Take the text after the last dot: =TEXTAFTER(A2,".",-1,,,""). The last argument returns blank for names with no extension.
How to remove the file extension from a file name in Excel
=TEXTBEFORE(A2,".",-1,,,A2)Keep the text before the last dot: =TEXTBEFORE(A2,".",-1,,,A2). Names without a dot come back unchanged.
How to create a URL slug from a title in Excel
=LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),":",""),"?",""),",",""),"'",""),"!","")," ","-"))Trim the title, remove punctuation with SUBSTITUTE, turn spaces into hyphens and make it lowercase: =LOWER(SUBSTITUTE(…TRIM(A2)…," ","-")).
How to build UTM tracking links in Excel
=A2&IF(ISNUMBER(FIND("?",A2)),"&","?")&"utm_source="&B2&"&utm_medium="&C2&"&utm_campaign="&D2Join the base URL and the parameters with &: =A2&IF(ISNUMBER(FIND("?",A2)),"&","?")&"utm_source="&B2&"&utm_medium="&C2&"&utm_campaign="&D2.
How to add text to the beginning of every cell in Excel
=$E$1&A2Join the prefix in front with &: =$E$1&A2, with the prefix in E1. Fill down to apply it to the whole column.
How to add text to the end of every cell in Excel
=A2&$E$1Join the suffix after the value with &: =A2&$E$1, with the suffix (for example " cm") in E1.
How to extract text before a character in Excel
=TEXTBEFORE(A2,$E$1)Use =TEXTBEFORE(A2,"-") in Excel 365. In any version, =LEFT(A2,FIND("-",A2)-1) returns everything before the first dash.
How to extract text between two characters in Excel
=TEXTBEFORE(TEXTAFTER(A2,$E$1),$G$1)Take the text after the first character, then the text before the second: =TEXTBEFORE(TEXTAFTER(A2,"@"),".").
How to extract text between parentheses in Excel
=IFERROR(TEXTBEFORE(TEXTAFTER(A2,"("),")"),"")Take what follows "(" and stops at ")": =IFERROR(TEXTBEFORE(TEXTAFTER(A2,"("),")"),""). IFERROR leaves rows without brackets blank.
How to extract text after the nth occurrence of a character in Excel
=TEXTAFTER(A2,"-",$E$1)Give TEXTAFTER the instance number: =TEXTAFTER(A2,"-",2) returns everything after the second dash.
How to find the position of the nth occurrence of a character in Excel
=FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),$E$1))Mark the nth occurrence with SUBSTITUTE and find the mark: =FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),$E$1)).
How to find the position of the last occurrence of a character in Excel
=FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))Count the characters, then mark the last one: =FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))).
How to extract the nth word from a text string in Excel
=INDEX(TEXTSPLIT(A2," "),$E$1)Split the text into words and pick one: =INDEX(TEXTSPLIT(A2," "),2) returns the second word. In older versions use the TRIM, MID and SUBSTITUTE trick.
How to extract the first N characters from a cell in Excel
=LEFT(A2,$E$1)Use =LEFT(A2,3) to return the first 3 characters. Put the number in a cell, as in =LEFT(A2,$E$1), to change it easily.
How to extract the last N characters from a cell in Excel
=RIGHT(A2,$E$1)Use =RIGHT(A2,4) to return the last 4 characters, or =RIGHT(A2,$E$1) with the count in a cell.
How to extract characters from the middle of a text string in Excel
=MID(A2,$E$1,$G$1)Use MID with a start position and a length: =MID(A2,5,4) returns 4 characters starting at the 5th.
How to remove the first N characters from a cell in Excel
=RIGHT(A2,LEN(A2)-$E$1)Keep everything except the first N characters: =RIGHT(A2,LEN(A2)-3), or =REPLACE(A2,1,3,"").
How to remove the last N characters from a cell in Excel
=LEFT(A2,LEN(A2)-$E$1)Keep everything except the last N characters: =LEFT(A2,LEN(A2)-3).
How to extract numbers from a text string in Excel
=--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,""))In Excel 365, keep only the characters that are digits and join them: =--CONCAT(IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")). In Google Sheets use =VALUE(REGEXREPLACE(A2,"\D","")).
How to split text into columns with a formula in Excel
=TEXTSPLIT(A2,", ")In Excel 365, =TEXTSPLIT(A2,", ") spills each part into its own column. In Google Sheets use =SPLIT(A2,", ",FALSE).
How to split comma-separated values into one column in Excel
=TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ")Join all the cells, then split the result into rows: =TEXTSPLIT(TEXTJOIN(", ",TRUE,B2:B6),,", ").
How to split text by line break in Excel
=TEXTSPLIT(A2,CHAR(10))Use CHAR(10), the line break character, as the delimiter: =TEXTSPLIT(A2,CHAR(10)) in Excel 365, =SPLIT(A2,CHAR(10)) in Google Sheets.
How to count words in a cell in Excel
=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)Count the spaces in the trimmed text and add one: =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1).
How to count occurrences of a word in a cell in Excel
=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1)Remove the word, compare lengths and divide by the word's length: =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1).
How to count items in a comma-separated list in Excel
=IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1)Count the commas and add one: =IF(TRIM(B2)="",0,LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1).
How to count the number of lines in a cell in Excel
=IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1)Count the line breaks and add one: =IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1).
How to combine cells with a comma and skip blanks in Excel
=TEXTJOIN(", ",TRUE,A2:E2)Use TEXTJOIN with TRUE as the second argument: =TEXTJOIN(", ",TRUE,A2:E2) puts ", " between the values and skips empty cells.
How to combine a column into one cell in Excel
=TEXTJOIN(", ",TRUE,A2:A7)Use =TEXTJOIN(", ",TRUE,A2:A7). It joins every value in the column with ", " between them and skips blanks.
How to combine text and a number without losing formatting in Excel
=A2&" sales: "&TEXT(B2,"$#,##0")&" ("&TEXT(C2,"+0.0%;-0.0%")&")"Wrap the number in TEXT with the format you want: =A2&" sales: "&TEXT(B2,"$#,##0"). The & operator alone drops currency signs, commas and percent signs.
How to add a line break in a formula in Excel
=A2&CHAR(10)&B2&CHAR(10)&C2Join CHAR(10) between the parts: =A2&CHAR(10)&B2&CHAR(10)&C2, then turn on Wrap Text for the cell.
How to add quotes around text in Excel
=""""&A2&""""Use two double quotes for each quote mark you want: =""""&A2&"""". Or use CHAR(34), the quote character: =CHAR(34)&A2&CHAR(34).
How to make a comma-separated list with quotes for a SQL query in Excel
="'"&TEXTJOIN("','",TRUE,A2:A6)&"'"Join the values with ',' between them and add the outer quotes: ="'"&TEXTJOIN("','",TRUE,A2:A6)&"'".
How to change text to uppercase in Excel
=UPPER(A2)Use =UPPER(A2). It returns the text with every letter in capitals; numbers and symbols stay as they are.
How to change text to lowercase in Excel
=LOWER(A2)Use =LOWER(A2). It returns the text with every letter in lowercase, which is the standard way to clean up email addresses.
How to convert text to sentence case in Excel
=UPPER(LEFT(TRIM(A2)))&LOWER(MID(TRIM(A2),2,LEN(A2)))Capitalize the first letter and lowercase the rest: =UPPER(LEFT(TRIM(A2)))&LOWER(MID(TRIM(A2),2,LEN(A2))).
How to use title case but keep small words lowercase in Excel
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(A2)," Of "," of ")," And "," and ")," The "," the ")," A "," a ")Apply PROPER, then lowercase the small words with SUBSTITUTE, matching them with a space on each side so the first word stays capitalized.
How to compare two cells with case sensitivity in Excel
=EXACT(A2,B2)Use =EXACT(A2,B2). It returns TRUE only when the two texts match exactly, capitals included; the = sign ignores case.
How to check if text is all uppercase in Excel
=EXACT(A2,UPPER(A2))Compare the text with its uppercase version using EXACT: =EXACT(A2,UPPER(A2)) returns TRUE when every letter is a capital.
How to replace the nth occurrence of a character in Excel
=SUBSTITUTE(A2,"-"," ",$E$1)Use SUBSTITUTE's fourth argument, the instance number: =SUBSTITUTE(A2,"-"," ",3) replaces only the third dash.
How to find and replace text with a formula in Excel
=SUBSTITUTE(A2,$E$1,$G$1)Use =SUBSTITUTE(A2,"old","new"). It replaces every occurrence of the old text, and the original cell stays unchanged.
How to replace multiple values at once with a formula in Excel
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,$D$2,$E$2),$D$3,$E$3),$D$4,$E$4),$D$5,$E$5)Nest one SUBSTITUTE per pair, reading the pairs from a small table: =SUBSTITUTE(SUBSTITUTE(A2,$D$2,$E$2),$D$3,$E$3)…
How to remove a specific word from a cell in Excel
=TRIM(SUBSTITUTE(A2,$E$1,""))Replace the word with nothing and tidy the spaces: =TRIM(SUBSTITUTE(A2,"Deluxe","")).
How to remove numbers from text in Excel
=TRIM(LET(c,MID(A2,SEQUENCE(LEN(A2)),1),CONCAT(IF(ISNUMBER(--c),"",c))))In Excel 365, keep only the characters that aren't digits: =TRIM(LET(c,MID(A2,SEQUENCE(LEN(A2)),1),CONCAT(IF(ISNUMBER(--c),"",c)))).
How to remove text in parentheses in Excel
=IFERROR(TRIM(REPLACE(A2,FIND("(",A2),FIND(")",A2)-FIND("(",A2)+1,"")),A2)Replace everything from "(" to ")" with nothing: =IFERROR(TRIM(REPLACE(A2,FIND("(",A2),FIND(")",A2)-FIND("(",A2)+1,"")),A2).
How to insert a character at a specific position in text in Excel
=REPLACE(A2,$E$1,0,$G$1)Use REPLACE with a length of 0, which inserts instead of replacing: =REPLACE(A2,4,0,"-") puts a dash before the 4th character.
How to remove the last word from a cell in Excel
=TEXTBEFORE(A2," ",-1)Keep the text before the last space: =TEXTBEFORE(A2," ",-1) in Excel 365.
How to check if a cell contains any word from a list in Excel
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No")Search for every word in the list at once and count the hits: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No").
How to categorize text based on keywords in Excel
=XLOOKUP(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),$E$2:$E$6,"Other")Return the category of the first keyword found: =XLOOKUP(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),$E$2:$E$6,"Other").
How to check if a cell contains all of several words in Excel
=IF(COUNT(SEARCH($D$2:$D$3,A2))=ROWS($D$2:$D$3),"Yes","No")Count how many of the words are found and compare with the number of words: =IF(COUNT(SEARCH($D$2:$D$3,A2))=ROWS($D$2:$D$3),"Yes","No").
How to check if a cell starts with specific text in Excel
=LEFT(A2,LEN($E$1))=$E$1Compare the first characters with the prefix: =LEFT(A2,LEN($E$1))=$E$1 returns TRUE when A2 starts with the text in E1.
How to check if a cell ends with specific text in Excel
=RIGHT(A2,LEN($E$1))=$E$1Compare the last characters with the ending: =RIGHT(A2,LEN($E$1))=$E$1 returns TRUE when A2 ends with the text in E1.
How to check if a cell contains a number in Excel
=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0Look for each digit 0 to 9 and count the hits: =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0. In Google Sheets use =REGEXMATCH(A2,"\d").
How to check if a cell contains only numbers in Excel
=REGEXTEST(A2,"^\d+$")In Excel 365 use =REGEXTEST(A2,"^\d+$"); in Google Sheets =REGEXMATCH(A2,"^\d+$"). Both are TRUE only when every character is a digit.
How to check if an email address is valid in Excel
=REGEXTEST(A2,"^[^@\s]+@[^@\s]+\.[A-Za-z]{2,}$")Test the format with a pattern: =REGEXTEST(A2,"^[^@\s]+@[^@\s]+\.[A-Za-z]{2,}$") in Excel 365, or REGEXMATCH with the same pattern in Google Sheets.
How to check if text matches a pattern in Excel
=REGEXTEST(A2,"^[A-Z]{3}-\d{4}$")Use REGEXTEST in Excel 365: =REGEXTEST(A2,"^[A-Z]{3}-\d{4}$") checks for three capital letters, a dash and four digits. Google Sheets uses REGEXMATCH.
How to find the position of a character in a text string in Excel
=FIND($E$1,A2)Use =FIND("@",A2). It returns the position of the first match, counting the first character as 1; SEARCH does the same but ignores case.
How to format phone numbers in Excel
="("&LEFT(A2,3)&") "&MID(A2,4,3)&"-"&RIGHT(A2,4)Cut the 10 digits into parts and join them with the punctuation: ="("&LEFT(A2,3)&") "&MID(A2,4,3)&"-"&RIGHT(A2,4) gives (612) 555-0142.
How to convert a date to text in Excel
=TEXT(A2,"yyyy-mm-dd")Use TEXT with a format code: =TEXT(A2,"yyyy-mm-dd") turns a date into text like 2026-03-04.
How to pad text with spaces to a fixed length in Excel
=LEFT(A2&REPT(" ",$E$1),$E$1)Add plenty of spaces and cut back to the width: =LEFT(A2&REPT(" ",12),12) makes every value exactly 12 characters.
How to show numbers in thousands (K) and millions (M) in Excel
=IF(B2>=1000000,TEXT(B2,"0.0,,")&"M",IF(B2>=1000,TEXT(B2,"0.0,")&"K",TEXT(B2,"0")))For a text label, pick the unit with IF and let TEXT scale the number: commas at the end of a format divide by 1,000 each, as in TEXT(B2,"0.0,,")&"M".
How to add st, nd, rd and th to numbers in Excel
=A2&IF(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),"th",CHOOSE(MIN(MOD(A2,10)+1,5),"th","st","nd","rd","th"))Use "th" for 11 to 13, otherwise pick the suffix from the last digit: =A2&IF(AND(MOD(A2,100)>=11,MOD(A2,100)<=13),"th",CHOOSE(MIN(MOD(A2,10)+1,5),"th","st","nd","rd","th")).
How to create an ID with a prefix and leading zeros in Excel
=$E$1&TEXT(A2,"00000")Pad the number with TEXT and join the prefix: =$E$1&TEXT(A2,"00000") turns 42 into INV-00042.
How to truncate text and add an ellipsis in Excel
=IF(LEN(A2)>$E$1,TRIM(LEFT(A2,$E$1-3))&"...",A2)Cut long text and add "...": =IF(LEN(A2)>20,TRIM(LEFT(A2,17))&"...",A2). Text within the limit stays unchanged.
How to mask all but the last 4 digits in Excel
=REPT("*",LEN(A2)-4)&RIGHT(A2,4)Repeat a mask character for all but the last 4 digits and join the end: =REPT("*",LEN(A2)-4)&RIGHT(A2,4).
How to repeat a character n times in Excel
=REPT("★",B2)&REPT("☆",5-B2)Use =REPT(text,number). For a 5-star rating: =REPT("★",B2)&REPT("☆",5-B2).
How to reverse text in a cell in Excel
=TEXTJOIN("",TRUE,MID(A2,SEQUENCE(LEN(A2),,LEN(A2),-1),1))In Excel 365, read the characters from last to first and join them: =TEXTJOIN("",TRUE,MID(A2,SEQUENCE(LEN(A2),,LEN(A2),-1),1)).
How to sort words in a cell alphabetically in Excel
=TEXTJOIN(", ",TRUE,SORT(TEXTSPLIT(A2,,", ")))Split the list into rows, sort it and join it back: =TEXTJOIN(", ",TRUE,SORT(TEXTSPLIT(A2,,", "))).
How to convert a letter to its position in the alphabet in Excel
=CODE(UPPER(A2))-64Use the character code: =CODE(UPPER(A2))-64 returns 1 for A, 2 for B, up to 26 for Z.
How to list the letters of the alphabet with a formula in Excel
=CHAR(SEQUENCE($D$1,1,CODE($F$1)))In Excel 365, generate the character codes with SEQUENCE and turn them into letters: =CHAR(SEQUENCE(26,1,65)) spills A to Z.
How to find the longest text in a column in Excel
=XLOOKUP(MAX(LEN(A2:A7)),LEN(A2:A7),A2:A7)Find the largest length and return the text that has it: =XLOOKUP(MAX(LEN(A2:A7)),LEN(A2:A7),A2:A7).
How to extract the ZIP code from an address in Excel
=TEXTAFTER(A2," ",-1)When the ZIP code is the last word, take the text after the last space: =TEXTAFTER(A2," ",-1). It keeps ZIP+4 codes whole.
How to extract hashtags from text in Excel
=LET(w,TEXTSPLIT(A2," "),TEXTJOIN(" ",TRUE,FILTER(w,LEFT(w)="#","")))In Excel 365, split the text into words and keep the ones that start with #: =LET(w,TEXTSPLIT(A2," "),TEXTJOIN(" ",TRUE,FILTER(w,LEFT(w)="#",""))).
How to extract a number from text in Google Sheets
=VALUE(REGEXEXTRACT(A2,"-?\d[\d,]*\.?\d*"))Use REGEXEXTRACT with a number pattern and wrap it in VALUE: =VALUE(REGEXEXTRACT(A2,"-?\d[\d,]*\.?\d*")). It keeps decimals, thousands commas and minus signs.
How to check if a cell contains one of several words in Google Sheets
=REGEXMATCH(A2,"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4))Join the words into one pattern with | and test it: =REGEXMATCH(A2,"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4)).
How to extract an email address from text in Google Sheets
=IFERROR(REGEXEXTRACT(A2,"[\w.+-]+@[\w-]+\.[\w.-]*\w"),"")Use REGEXEXTRACT with an email pattern: =IFERROR(REGEXEXTRACT(A2,"[\w.+-]+@[\w-]+\.[\w.-]*\w"),"").
How to extract several parts of text into columns with REGEXEXTRACT in Google Sheets
=REGEXEXTRACT(A2,"^([A-Z]+)-(\d{4})-(\d+)$")Put each part you want in its own brackets: =REGEXEXTRACT(A2,"^([A-Z]+)-(\d{4})-(\d+)$") returns one column per bracket.
How to clean a whole column of names with one formula in Google Sheets
=ARRAYFORMULA(PROPER(TRIM(A2:A7)))Wrap TRIM and PROPER in ARRAYFORMULA so they run on the whole range: =ARRAYFORMULA(PROPER(TRIM(A2:A7))).
How to split text by a multi-character delimiter in Google Sheets
=SPLIT(A2," - ",FALSE)Set SPLIT's third argument to FALSE so the whole delimiter is used: =SPLIT(A2," - ",FALSE). By default SPLIT splits at each character.
How to fix the REGEXEXTRACT "expects text values" error in Google Sheets
=REGEXEXTRACT(TO_TEXT(A2),"^\d{3}")REGEXEXTRACT, REGEXMATCH and REGEXREPLACE only accept text. Convert numbers first: =REGEXEXTRACT(TO_TEXT(A2),"^\d{3}").
How to extract text between two words in Google Sheets
=IFERROR(REGEXEXTRACT(A2,$E$1&" (.+?) "&$G$1),"")Capture what sits between the words with a lazy group: =REGEXEXTRACT(A2,"for (.+?) shipped"). Build the pattern from cells to change the words easily.