Text Functions in Excel

How to extract last two words from text string in Excel

To extract the last two words from a cell, you can use a formula built with several Excel functions, including MID, FIND, SUBSTITUTE, and LEN. Formula =MID(A1,FIND(“@”,SUBSTITUTE(A1,” “,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))-1))+1,100) Explanation In the example shown, the formula in C5 is: =MID(B5,FIND(“@”,SUBSTITUTE(B5,” “,”@”,LEN(B5)-LEN(SUBSTITUTE(B5,” “,””))-1))+1,100) How this formula works At the core, this formula uses the MID function to …

How to extract multiple lines from a cell in Excel

To extract lines from a multi-line cell, you can use a clever (and intimidating) formula that combines 5 Excel functions: SUBSTITUTE, REPT, TRIM, MID, and LEN. Formula =TRIM(MID(SUBSTITUTE(A1,delim,REPT (” “,LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1))) Explanation In the example shown, the formula in D5 is: =TRIM(MID(SUBSTITUTE($C5,CHAR(10),REPT (” “,LEN($C5))), (D$4-1)*LEN($C5)+1, LEN($C5))) How this formula works At the core, this formula …

How to extract nth word from text string in excel

If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: MID, SUBSTITUTE, TRIM, REPT, and LEN. Formula =TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1))) Explanation How this formula works At the core, this formula takes …

How to extract substring in Excel

To extract a substring with an Excel formula, you can use the MID function. Note: In this example, we are calculating the end position in order to extract a substring with a literal start and end position. However, if you know the number of characters to extract, you can just plug in that number directly. Formula …

How to extract word containing specific text in Excel

To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or other substrings with a unique id. Formula =TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,99)), MAX(1,FIND(“@”,SUBSTITUTE (A1,” “,REPT(” “,99)))-50),99)) Explanation In the example shown, …

How to extract text between parentheses in Excel

To extract text between parentheses, braces, brackets, etc. you can use a formula based on the MID function, with help from SEARCH function. Formula =MID(text,SEARCH(“(“,text)+1,SEARCH(“)”, text)-SEARCH(“(“,text)-1) Explanation In the example shown, the formula in C5 is: =MID(B5,SEARCH(“(“,B5)+1,SEARCH (“)”,B5)-SEARCH(“(“,B5)-1)+0 How this formula works The foundation of this formula is the MID function, which extracts a specific …

Extract word that begins with specific character in Excel

To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user name from a cell that contains other text. Formula =TRIM(LEFT(SUBSTITUTE(MID(text,FIND(“@”, txt),LEN(text)),” “,REPT(” “,100)),100)) Note: 100 represents the longest …

How to count total characters in a range in Excel

If you want to count the total characters in a range of cells, you can do with a formula that uses LEN, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(rng)) Explanation In the example, the active cell contains this formula: =SUMPRODUCT(LEN(B3:B6)) Here’s how the formula works: SUMPRODUCT accepts the range B3:B6 as an array of four …

How to count number of characters of text in a cell in Excel

If you need to count the total characters in a cell, you can do so using the LEN function. Formula =LEN(a1) Explanation In the example, the formula in the active cell is: =LEN(B3) The LEN function simply counts all characters that appear in a cell. All characters are counted, including space characters. Numbers are also …

How to count specific words in a range in Excel

To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions. Formula =SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,text,””)))/LEN(text)) Explanation In the example shown, the formula in C11 is: =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,””)))/LEN(C2)) Note: The formula on this page counts instances of a word in a …

How to count specific words in a cell in Excel

If you need to count how many times a specific a word (or any substring) appears inside a cell, you can use a formula that uses SUBSTITUTE and LEN. Formula =(LEN(text)-LEN(SUBSTITUTE(text,word,””)))/LEN(word) Explanation In our example, we are using this formula: =(LEN(B4)-LEN(SUBSTITUTE(B4,C4,””)))/LEN(C4) How this formula works B4 is the cell we’re counting words in, and C4 …

How to count specific characters in a range in Excel

If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,text,””))) Explanation In the example, the active cell contains this formula: =SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,”o”,””))) How the formula works For each cell in the range, SUBSTITUTE removes all …

How to count specific characters in a cell in Excel

To count how many times a specific character appears in a cell, you can use a formula based on the SUBSTITUTE and LEN functions. Formula =LEN(A1)-LEN(SUBSTITUTE(A1,”a”,””)) Explanation In the example, the active cell contains this formula: =LEN(B3)-LEN(SUBSTITUTE(B3,C3,””)) How this formula works This formula works by using SUBSTITUTE to first remove all of the characters …

How to get last line in cell in Excel

To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions. Formula =TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(” “,200)),200)) Note: 200 is an arbitrary number that represents the longest line you expect to find in a cell. If you have longer lines, increase this number as needed. Explanation In …

How to get first word in Excel

If you need to extract the first word from some text you can use a formula that uses the FIND and LEFT functions. Formula =LEFT(a1,FIND(” “,a1)-1) Explanation From the example, the formula looks like this: =LEFT(B4,FIND(” “,B4)-1) How this formula works FIND returns the position (as a number) of the first occurrence of a space character in …

How to find nth occurrence of character in Excel

To find the nth occurrence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions. Formula =FIND(CHAR(160),SUBSTITUTE (text,”@”,CHAR(160),N)) Explanation In the example shown, the formula in D5 is: =FIND(CHAR(160),SUBSTITUTE (B5,”@”,CHAR(160),C5)) How this formula works In this example we are looking for the nth occurrence of the …

How to find and replace multiple values at same time in Excel

To find and replace multiple values with a formula, you can nest multiple SUBSTITUTE functions together, and feed in find/replace pairs from another table using the INDEX function. Formula =SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)) Explanation In the example shown, we are performing 4 separate find and replace operations. The formula in G5 is: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1), INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4)) where “find” is …

How to get last word in a cell in Excel

To get the last word from a text string, you can use a formula based on the TRIM, SUBSTITUTE, RIGHT, and REPT functions. Formula =TRIM(RIGHT(SUBSTITUTE(text,” “,REPT(” “,100)),100)) Explanation In the example shown, the formula in C6 is: =TRIM(RIGHT(SUBSTITUTE(B6,” “,REPT(” “,100)),100)) Which returns the word “time”. How this formula works This formula is an interesting example …

How to pad text to match equal length in Excel

To pad text to an equal length using another character, you can use a formula based on the REPT and LEN functions. Formula =A1&REPT(“*”,count-LEN(A1)) Explanation In the example shown, a formula is used to append a variable number of asterisks (*) to values in column B so that the final result is always 12 characters in …

Normalize text by removing punctuations, extra spaces and more in Excel

To remove some of the natural complexity of text (strip punctuation, normalize case, remove extra spaces) you can use a formula based on the SUBSTITUTE function, with help from the TRIM and LOWER functions. Instance There may be times when you need to remove some of the variability of text before other processing. Case Study …

Extract most frequently occurring text in Excel

To extract the word or text value that occurs most frequently in a range, you can use a formula based on several functions INDEX, MATCH, and MODE. Formula =INDEX(range,MODE(MATCH(range,range,0))) Explanation In the example shown, the formula in H5 is: =INDEX(B5:F5,MODE(MATCH(B5:F5,B5:F5,0))) Working from the inside out, the MATCH function matches the range against itself. That is, …

Find most frequent text within a range with criteria in Excel

To find the most frequently occurring text in a range, based on criteria you supply, you can use an array formula based on several Excel functions MATCH, MODE, INDEX, and IF. Formula =INDEX(range1,MODE(IF(range2=criteria, MATCH(range1,range1,0)))) Note: this is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula …

Join cells with comma in Excel

To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. Formula =SUBSTITUTE(TRIM(A1&” “&B1&” “&C1&” “&D1&” “&E1),” “,”, “) Explanation In the example shown, the formula in G5 is: =SUBSTITUTE(TRIM(B5&” “&C5&” “&D5&” “&E5&” “&F5),” …

Remove leading and trailing spaces from text in one or more cells in Excel

If you need to strip leading and trailing spaces from text in one or more cells, you can use the TRIM function. Formula =TRIM(text) Explanation In the example show, the formula in cell C3 is: =TRIM(B3) Once you’ve removed extra spaces, you can copy the cells with formulas and paste special elsewhere as “values” to …

Remove first character in a cell in Excel

To remove the first character in a cell, you can use the REPLACE function. Formula =REPLACE(A1,1,N,””) Explanation In the example shown, the formula in D5 is: =REPLACE(A1,1,1,””) How this formula works This formula uses the REPLACE function to replace the first character in a cell with an empty string (“”). The arguments for REPLACE are …

Remove file extension from filename in Excel

To remove a file extension from a file name, you can use a formula based on the LEFT and FIND functions. Formula =LEFT(filename,FIND(“.”,filename)-1) Note: because this formula finds the first occurrence of “.”, it will remove all file extensions when there are are more than one. Explanation In the example shown, the formula in C5 …

Remove last characters from right in a cell in Excel

To remove the last n characters from a text string, you can use a formula based on the LEFT and LEN functions. You can use a formula like this to strip the last 3 characters, last 5 characters of a value, starting on the left. Formula =LEFT(text,LEN(text)-n) Note: there is no reason to use the …

Get position of 2nd 3rd and more instance of character in Excel

To get the position of the 2nd, 3rd, 4th, etc. instance of a specific character inside a text string, you can use the FIND and SUBSTITUTE functions. Note: we use “~” in this case only because it rarely occurs in other text. You can use any character that you know won’t appear in the text. …

Remove unwanted characters in Excel

To remove specific unwanted characters in Excel, you can use a formula based on the SUBSTITUTE function. Formula =SUBSTITUTE(B4,CHAR(code),””) Explanation In the example shown, the formula in C4 is: =SUBSTITUTE(B4,CHAR(202),””) Which removes a series of 4 invisible characters at the start of each cell in column B. How this formula works The SUBSTITUTE function can …

Remove text by variable position in a cell in Excel

To remove text from a cell when the text is at a variable position, you can use a formula based on the REPLACE function, with help from the FIND function. Formula =REPLACE(text,start,FIND(marker,text)+1,””) Explanation In the example shown, the formula in C6 is: =REPLACE(B6,1,FIND(“:”,B6)+1,””) How this formula works The REPLACE function will replace text by position. …

Remove text by position in a cell in Excel

To remove text from a cell by position, you can use the REPLACE function. Formula =REPLACE(text,start,characters,””) Explanation In the example shown, the formula in C6 is: =REPLACE(B6,1,24,””) How this formula works The replace function lets you replace text based on its location and length. In this case, we want to strip off the drive and …

Remove text by matching in a cell in Excel

To remove text from a cell based by matching content (not location), you can use the SUBSTITUTE function. Formula =SUBSTITUTE(B6,text_to_remove,””) Explanation In the example shown, the formula in C6 is: =SUBSTITUTE(B6,”-“,””) How this formula works The SUBSTITUTE function lets you replace text by matching content. In this case, we want to remove hyphens from telephone …

Remove line breaks in a cell in Excel

To remove line breaks from a cell, or from text inside a formula, you can use a formula based on the SUBSTITUTE and CHAR functions. Formula =SUBSTITUTE(A1,CHAR(10),”, “) Explanation In the example shown, the formula in C5 is: =SUBSTITUTE(B5,CHAR(10),”, “) which replaces line breaks in B5 with commas. How this formula works First, you should …

Split dimensions into three parts in Excel

To split dimensions that like 100x50x25 into three separate parts, you can use some rather complicated formulas that use LEFT, MID, RIGHT, FIND, LEN, and SUBSTITUTE. Note: you can also use Flash Fill in Excel 2013 and above, and the “text to columns” feature in older versions of Excel. Both approaches are quite a bit …

Replace one character with another in Excel

To replace or substitute all occurrences of one character with another character, you can use the SUBSTITUTE function. Formula =SUBSTITUTE(ref,old,new) Explanation In the example shown, the formula in C6 is: =SUBSTITUTE(B6,” “,”-“) How this formula works The SUBSTITUTE function is full automatic. All you need to do is supply “old text” and “new text”. SUBSTITUTE …

How to translate letters to numbers in Excel

To translate letters in a string to numbers, you can use an array formula based on the TEXTJOIN and VLOOKUP functions, with a defined translation table to provide the necessary lookups. Formula {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(A1,ROW (INDIRECT(“1:”&LEN(A1))),1))),xtable,2,0))} Explanation In the example shown, the formula in C5 is: {=TEXTJOIN(“”,1,VLOOKUP(T(IF(1,MID(B5,ROW (INDIRECT(“1:”&LEN(B5))),1))),xtable,2,0))} where “xtable” is the named range E5:F10. Note: this is an array …

How to strip numeric characters from cell in Excel

To remove numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. Formula {=TEXTJOIN(“”,TRUE,IF(ISERR(MID(A1, ROW(INDIRECT(“1:100”)),1)+0), MID(A1,ROW(INDIRECT(“1:100″)),1),””))} Explanation In the example shown, the formula in C5 is: =TEXTJOIN(“”,TRUE,IF(ISERR(MID(B5, ROW(INDIRECT(“1:100”)),1)+0), MID(B5,ROW(INDIRECT(“1:100″)),1),””)) Note: this is an array formula and must be entered with control + shift + …

How to strip non-numeric characters in Excel

To remove non-numeric characters from a text string, you can try this experimental formula based on the TEXTJOIN function, new in Excel 2016. Formula {=TEXTJOIN(“”,TRUE,IFERROR(MID(A1,ROW (INDIRECT(“1:100″)),1)+0,””))} Note: TEXTJOIN will return the numbers as text, for example “100,”500″, etc. If you want a true numeric result, add zero, or wrap the entire formula in the VALUE function. TEXTJOIN was added …

How to strip html from text or numbers in Excel

To strip html or other markup from values in cells, you can use the MID function. Formula =MID(text,start,LEN(text)-markup_len) Explanation In the example shown, the formula in C5 is: =MID(B5,4,LEN(B5)-7) How this formula works The MID function returns characters using a fixed starting point and ending point. In this case, the markup consists of the html …

How to split text with delimiter in Excel

To split text at an arbitrary delimiter (comma, space, pipe, etc.) you can use a formula based on the TRIM, MID, SUBSTITUTE, REPT, and LEN functions. Formula =TRIM(MID(SUBSTITUTE(A1,delim,REPT (” “,LEN(A1))),(N-1)*LEN(A1)+1,LEN(A1))) Explanation In the example shown, the formula in C5 is: =TRIM(MID(SUBSTITUTE($B5,”|”, REPT(” “,LEN($B5))),(C$4-1)* LEN($B5)+1,LEN($B5))) Note: references to B5 and C4 are mixed references to allow the formula …

Data Analysis

References

Data Validations