Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: LEN function

LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation

What are LEN, LENB functions in Excel? LEN, LENB functions are TEXT functions in Microsoft Excel. LEN returns the number of characters in a text string.While, LENB returns the number of bytes used to represent the characters in a text string. Important: These functions may not be available in all languages. LENB counts 2 bytes per character only when a DBCS…

Basic outline numbering in Excel

This tutorial shows how to create 1st and 2nd level outline formatting using excel formulas. To generate basic outline numbering you can use COUNTA, MID, FIND, IF, and LEN functions. Note: The formula used will only handle a 2-level outline. Explanation In the example shown, the formula in D5 is: =COUNTA($B$5:B5)&”.”&IF(B5<>””,1, MID(D4,FIND(“.”,D4)+1,LEN(D4))+1) How this formula works At the core, this formula builds…

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 to be copied across and…

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 bold tag, which appears at…

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 formula and must be entered with…

How to get sheet name only in Excel

If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this: Sheet1 Formula =MID(CELL(“filename”,A1),FIND(“]”, CELL(“filename”,A1))+1,255) Explanation How the formula works The cell function is used to…

How to remove trailing slash from url in Excel

To remove a trailing slash from a URL or path, you can use a formula based on the LEFT and LEN functions. Formula =LEFT(url,LEN(B4)-(RIGHT(url)=”/”)) Explanation In the example shown, the formula in cell C6 is: =LEFT(B4,LEN(B4)-(RIGHT(B4)=”/”)) How this formula works At the core, this formula uses the LEFT function to return text starting from the left. To work out how…

How to strip protocol and trailing slash from URL in Excel

To remove the protocol (i.e. http://, ftp://, etc.) and trailing slash from a URL, you can use a formula based on the MID, FIND, and LEN functions. Formula =MID(url,FIND(“//”,url)+2,LEN(url)-FIND(“//”,url)-1-(RIGHT(url)=”/”)) Explanation In the example shown, the formula in C5 is: =MID(B4,FIND(“//”,B4)+2,LEN(B4)-FIND(“//”,B4)-1-(RIGHT(B4)=”/”)) How this formula works The core of this formula is the MID function, which extracts the text in a URL…

How to get top level domain (TLD) in Excel

To extract the top level domain (called “TLD”)  from a list of domain names or email addresses, you can use a rather complex formula that uses several functions. In the formula below, domain represents a domain or email address in normal “dot” syntax. Formula =RIGHT(domain,LEN(domain)-FIND(“*”,SUBSTITUTE(domain,”.”,”*”, LEN(domain)-LEN(SUBSTITUTE(domain,”.”,””))))) Explanation In the example, the active cell contains this formula: =RIGHT(B4,LEN(B4)-FIND(“*”,SUBSTITUTE(B4,”.”,”*”,LEN(B4)-LEN(SUBSTITUTE(B4,”.”,””))))) How the formula works:…

Extract last name from full name — Manipulating NAMES in Excel

If you need extract the last name from a full name, you can do so with this rather complex formula that uses several functions. Note: In the  formula below, name is a full name, with a space separating the first name from other parts of the name. Formula =RIGHT(name,LEN(name)-FIND(“*”,SUBSTITUTE(name,” “,”*”, LEN(name)-LEN(SUBSTITUTE(name,” “,””))))) Important! Handling inconsistent spaces Extra spaces will cause…

Get first name from name with comma — Manipulating NAMES in Excel

To extract the first name from a full name in “Last, First” format, you can use a formula that uses RIGHT, LEN and FIND functions. Note: this formula will only work with names in Last, First format, separated with a comma and space. Formula =RIGHT(name,LEN(name)-FIND(“, “,name)-1) Explanation From the table and formula (above), name represent full name in this format: LAST,…

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 simpler than the formulas described…

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 configured as follows: old_text is the original…

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 VALUE function if you don’t…

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 length. The formula in C5…

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, the formula in C5 is:…

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 range. For example, if a cell…

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 being counted in the source…

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 cells. For each cell in…

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 counted in their raw form…

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 contains the substring (word or…

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 word you expect to find…

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 a text string with spaces,…

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 looks for a line delimiter…

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 extract characters starting at the second…

How to count total words in a range in Excel

If you want to count the total words in a range of cells, you can do with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(range,” “,””))+1) Note: The formula inside SUMPRODUCT will return 1 even if a cell is empty. If you need to guard against this problem, you can add another array to SUMPRODUCT…

How to count total words in a cell in Excel

To count the total words in a cell, you can use a formula based on the LEN and SUBSTITUTE functions. Formula =LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1 Explanation In the example shown, C3 contains this formula: =LEN(TRIM(B3))-LEN(SUBSTITUTE(B3,” “,””))+1 How the formula works SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This number is then subtracted from…

How to Capitalize first letter in a sentence in Excel

This tutorial shows how to capitalize first letter in Excel. In Microsoft office word it is called sentence case, in Excel to capitalize the first letter in a word or string, you can use a formula based on the LEFT, MID, and LEN functions. Formula =UPPER(LEFT(A1))&MID(A1,2,LEN(A1)) Explanation  In the example shown, the formula in C5 is: =UPPER(LEFT(B5))&MID(B5,2,LEN(B5)) How this formula works…

How to abbreviate names or words in Excel

To abbreviate text that contains capital letters, you can try this array formula based on the TEXTJOIN function, which is new in Excel 2016. You can use this approach to create initials from names, or to create acronyms. Only capital letters will survive this formula, so the source text must include capitalized words. You can use the PROPER function to capitalize words…

How to count line breaks in cell in Excel worksheet

This tutorial shows how to count line breaks in cell in Excel worksheet using example below. To count total lines in a cell, you can use a formula based on the LEN, SUBSTITUTE, and CHAR functions. Formula =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 Explanation In the example, we are using this formula: =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 How the formula works First, the LEN function counts total characters in the cell…

How to convert text string to array in Excel

To convert a string to an array that contains one item for each letter, you can use an array formula based on the MID, ROW, LEN and INDIRECT functions. This can sometimes be useful inside other formulas that manipulate text at the character level.  Formula {=MID(string,ROW(INDIRECT(“1:”&LEN(string))),1)} Note: this is an array formula and must be entered with control + shift…

Excel Data validation specific characters only

Set criteria to accept specific characters only To use data validation to allow a list of specific characters only, you can use a rather complicated array formula based on the COUNT, MATCH, and LEN functions. Formula =COUNT(MATCH(MID(A1,ROW(INDIRECT (“1:”&LEN(A1))),1),allowed&””,0))=LEN(A1) Explanation In the example shown, data validation is applied with this formula: =COUNT(MATCH(MID(B5,ROW(INDIRECT (“1:”&LEN(B5))),1),allowed&””,0))=LEN(B5) where “allowed” is the named range D5:D11. How this formula…

Highlight numbers that include symbols in Excel

This tutorial shows how to Highlight numbers that include symbols in Excel using the example below; Formula =IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)=”<“,(MID(B4,2,LEN(B4))+0)<input)) Explanation To highlight numbers less than a certain value, including numbers entered as text like “<9”, “<10”, etc., you can use conditional formatting with a formula strips the symbols as needed and handles the result as a number. In the example shown,…

Highlight cells that end with in Excel

This tutorial shows how to Highlight cells that end with in Excel using the example below; Formula =COUNTIF(A1,”*text”) Explanation Note: Excel contains many built-in rules for highlighting values with conditional formatting, including a rule to highlight cells that end with specific text. However, if you want more flexibility, you can use your own formula, as explained in this article. If you…

Highlight blank cells in Excel

This tutorial shows how to Highlight blank cells in Excel using the example below; Formula =ISBLANK(A1) Explanation If you want to highlight cells that are blank or empty with conditional formatting, you can do so with a simple formula based on the ISBLANK function. For example, if you want to highlight blank cells in the range B4:G11, just select the range…

Find longest string with criteria in Excel

This tutorial shows how to Find longest string with criteria in Excel using the example below; Formula {=INDEX(range1,MATCH(MAX(LEN(range1)*(range2=criteria)),LEN(range1)*(range2=criteria),0))} Explanation To find the longest string in a range with criteria, you can use an array formula based on INDEX, MATCH, LEN and MAX. In the example shown, the formula in F6 is: {=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0))} Where “names” is the named range C5:C14, and…

Find longest string in column in Excel

This tutorial shows how to Find longest string in column in Excel using the example below; Formula {=INDEX(range,MATCH(MAX(LEN(range)),LEN(range),0))} Explanation To find the longest string (name, word, etc.) in a column, you can use an array formula based on INDEX and MATCH, together with LEN and MAX. In the example shown, the formula in F6 is: {=INDEX(names,MATCH(MAX(LEN(names)),LEN(names),0))} Where “names” is the named…

Count cells that are not blank in Excel

This tutorial shows how to Count cells that are not blank in Excel using the example below; Formula =COUNTA(range) Explanation To count the number of cells that are not blank in a range, you can use the COUNTA function. In the example shown, E5 contains this formula: =COUNTA(B4:B8) How this formula works COUNTA is fully automatic. When given a range of…

How to Remove Leading Spaces in Excel

The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces. 1. The TRIM function below removes 2 leading spaces, 3 extra spaces and 2 trailing spaces. Note: the TRIM function does not remove single spaces between words. 2. To get the length of a string, use the LEN function. Explanation: the…

How to Separate Text Strings in Excel

This example teaches you how to separate strings in Excel. Asides from using ‘Convert Text to Columns Wizard’ in data tab, you can as well follow the steps below to Separate Text Strings in Excel . The problem we are dealing with is that we need to tell Excel where we want to separate the string. In case of Smith, Mike the comma is…