Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Data bars Examples in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How To Filter Data in Excel
  • How to create Gauge Chart in Excel
  • Understanding Pivot Tables in Excel

References

  • Perform case-sensitive Lookup in Excel
  • How to get address of named range in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • How to use Excel INDIRECT Function
  • How to use Excel MMULT Function

Data Validations

  • Excel Data validation no punctuation
  • Excel Data validation don’t exceed total
  • Excel Data validation number multiple 100
  • Excel Data validation only dates between
  • Excel Data validation date in next 30 days

Tag: LEN function

Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel

by

Excel has many functions to offer when it comes to manipulating text strings. Formula tab → Function Library Group → Text Function N/B: Every formula in excel must begin with an equal sign, function then in bracket; cell name/cell ranges/cell references. Syntax: =fx(cell name/cell ranges/cell references) Example: = Sum( A1:B4) Where Sum is the function name and A1:B4 is …

Continue Reading

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

by

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 …

Continue Reading

Basic outline numbering in Excel

by

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 …

Continue Reading

Remove first character in a cell in Excel

by

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 …

Continue Reading

Remove last characters from right in a cell in Excel

by

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 …

Continue Reading

Split dimensions into three parts in Excel

by

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 …

Continue Reading

How to count total words in a cell in Excel

by

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 …

Continue Reading

How to translate letters to numbers in Excel

by

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 …

Continue Reading

How to count total words in a range in Excel

by

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 …

Continue Reading

How to strip html from text or numbers in Excel

by

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 …

Continue Reading

How to extract last two words from text string in Excel

by

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 …

Continue Reading

How to split text with delimiter in Excel

by

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 …

Continue Reading

How to extract multiple lines from a cell in Excel

by

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 …

Continue Reading

How to extract nth word from text string in excel

by

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 …

Continue Reading

How to extract word containing specific text in Excel

by

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, …

Continue Reading

Extract word that begins with specific character in Excel

by

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 …

Continue Reading

How to count total characters in a range in Excel

by

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 …

Continue Reading

Extract last name from full name — Manipulating NAMES in Excel

by

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 …

Continue Reading

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

by

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 …

Continue Reading

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

by

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 …

Continue Reading

How to count specific words in a range in Excel

by

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 …

Continue Reading

How to count specific words in a cell in Excel

by

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 …

Continue Reading

How to count specific characters in a cell in Excel

by

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 …

Continue Reading

How to pad text to match equal length in Excel

by

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 …

Continue Reading

How to get sheet name only in Excel

by

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 …

Continue Reading

How to strip protocol and trailing slash from URL in Excel

by

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 …

Continue Reading

How to remove trailing slash from url in Excel

by

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 …

Continue Reading

How to get top level domain (TLD) in Excel

by

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: …

Continue Reading

How to Capitalize first letter in a sentence in Excel

by

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: …

Continue Reading

How to convert text string to array in Excel

by

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 …

Continue Reading

How to count line breaks in cell in Excel worksheet

by

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 …

Continue Reading

How to abbreviate names or words in Excel

by

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 …

Continue Reading

Excel Data validation specific characters only

by

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 …

Continue Reading

Highlight blank cells in Excel

by

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 …

Continue Reading

Highlight cells that end with in Excel

by

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 …

Continue Reading

Highlight numbers that include symbols in Excel

by

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 …

Continue Reading

Find longest string in column in Excel

by

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: …

Continue Reading

Find longest string with criteria in Excel

by

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 …

Continue Reading

Count cells that are not blank in Excel

by

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. …

Continue Reading

How to Remove Leading Spaces in Excel

by

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 …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel OR Function
  • How to use IFS function in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel

Date Time

  • How to get Weekdays, Working days between Two Dates in Excel
  • Get first day of previous month in Excel
  • How to calculate nth day of year in Excel
  • Calculate expiration date in Excel
  • How to get same date next year or previous year in Excel

Grouping

  • Running count group by n size in Excel
  • Group numbers at uneven intervals in Excel
  • Map text to numbers in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel

General

  • How to fill cell ranges with random number from fixed set of options in Excel
  • Basic numeric sort formula in Excel
  • How to get original price from percentage discount in Excel
  • How to increase by percentage in Excel
  • List worksheet index numbers in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning