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

Data Analysis

  • Number and Text Filters Examples in Excel
  • How to do a t-Test in Excel?
  • Error Bars in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to count table columns in Excel

References

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to create dynamic named range with OFFSET in Excel
  • How to use Excel OFFSET function
  • Merge tables with VLOOKUP in Excel
  • Vlookup Examples in Excel

Data Validations

  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between
  • Excel Data validation must begin with
  • Excel Data validation allow weekday only
  • Prevent invalid data entering in specific cells

Category: Text Functions

Excel TEXT functions return a number in a specified number format

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

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

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

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

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 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 create email address from name in Excel

by

To build email addresses using first and last names, you can use a formula that concatenates values, with help from the LOWER and LEFT functions as needed. See example below: Formula =LOWER(LEFT(first)&last)&”@domain.com” Explanation In the example shown, the formula in D5 is: =LOWER(LEFT(C5)&B5)&”@”&”acme.com” How this formula works For a name like “Tim Brown”, this formula …

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 get page from URL in Excel

by

To extract the page, or the part of a path after the last forward slash (/), you can use a formula based on several Excel functions: TRIM, RIGHT, SUBSTITUTE, and REPT. Formula =TRIM(RIGHT(SUBSTITUTE(url,”/”,REPT(” “,100)),100)) Explanation In the example shown, the formula in C5 is: =TRIM(RIGHT(SUBSTITUTE(B5,”/”,REPT(” “,100)),100)) How this formula works At the core, this formula …

Continue Reading

How to extract name from email address in Excel

by

If want to extract the name part of an email address, you can do so with a formula that uses the LEFT and FIND functions. In the formula below, email represents the email address you are working with. Formula =LEFT(email,FIND(“@”,email)-1) Explanation In the example, we are using this formula: =LEFT(C4,FIND(“@”,C4)-1) Here’s how the formula works: …

Continue Reading

How to extract domain name from URL in Excel

by

If you want to extract the domain name from a complete URL, you can do so with a formula that uses  the LEFT and FIND functions. See example: In the formula below, url is the the URL you are working with. Formula =LEFT(url,FIND(“/”,url,9)) Explanation In the example, we are using this formula: =LEFT(B4,FIND(“/”,B4,9)) Here’s how …

Continue Reading

How to extract domain from email address in Excel

by

Atimes a user may want to extract the domain from an email address, the RIGHT, LEN, and FIND functions can be used to achieve that. In the formula below, email represents the email address you are working with. Formula =RIGHT(email,LEN(email)-FIND(“@”,email)) Explanation In the example, we are using this formula: =RIGHT(C4,LEN(C4)-FIND(“@”,C4)) Here’s how the formula works …

Continue Reading

How to create email address with name and domain in Excel

by

Atimes a user may want to build an email address from a first and last name. In that case, use a formula based on simple concatenation with help from the LEFT and LOWER functions. See illustration below: Formula =LOWER(LEFT(first)&last&”@”&domain) Explanation In the example shown, the formula in D5 is: =LOWER(LEFT(B5)&C5&”@”&$G$6) How this formula works Working …

Continue Reading

Put names into proper case — Manipulating NAMES in Excel

by

If you need to clean up names that are not in a proper case, you can use a simple formula based on PROPER functions. Formula =PROPER(name) Explanation In the example the formula in C5 is: =PROPER(B5) How this formula works The PROPER function automatically reformats text so that all words are capitalized. At the same …

Continue Reading

How to count keywords in a range of cell

by

To count the number of specific words or keywords that appear in a given cell, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions.  Formula =SUMPRODUCT(–ISNUMBER(SEARCH(keywords,A1))) Note: if a keyword appears more than once in a given cell, it will only be counted once. In other words, the formula only 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

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 add a line break with a formula while joining text strings in Excel

by

To add a line break with a formula, you can use the concatenation operator (&) along with the CHAR function. Note: make sure you have text wrap enabled on cells that contain line breaks. Formula =”text”&CHAR(10)&”text” Explanation In the example shown the formula in E4 is: =B4&CHAR(10)&C4&CHAR(10)&D4 How this formula works This formula “glues together” …

Continue Reading

Add line break based on OS in Excel

by

To add a line break taking into account the current OS (Mac or Windows), you can use the INFO function to test the system and then return the correct break character — CHAR(10) for Windows, CHAR(13) for Mac. Note: make sure you have text wrap enabled on cells that contain line breaks. Formula =IF(INFO(“system”)=”mac”,CHAR(13),CHAR(10)) Explanation The character …

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 check if cell contains all of many things in Excel

by

If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions. Case study: Let’s say you have a list of text strings in the range B5:B8, and you want …

Continue Reading

How to check if cell contains number in Excel

by

To test if a cell (or any text string) contains a number, you can use the FIND function together with the COUNT function. In the generic form of the formula (above), A1 represents the cell you are testing. The numbers to be checked (numbers between 0-9) are supplied as an array. Formula =COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A1))>0 Explanation In …

Continue Reading

How to check if cell contains one of many things in Excel

by

This tutorial shows how to check if cell contains one of many things in Excel using example below: If you want to test a cell to see if it contains one of several things, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER and SUMPRODUCT functions. Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 …

Continue Reading

How to check if cell contains some words but not others in Excel

by

To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function. Formula {=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0)} Explanation In the example shown, the formula in C5 is: {=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0)} This formula returns TRUE when B5 contains any of the words …

Continue Reading

How to check cell contains which things in Excel

by

If you have a list of things (words, substrings, etc) and want to find out which of these things appear in a cell, you can build a simple table and use a formula based on the SEARCH function. Setup Suppose you have a cells that contain text that mentions various colors, and you want to …

Continue Reading

Clean and reformat telephone numbers using SUBSTITUTE function in Excel

by

One way to clean up and reformat telephone numbers is to strip out all extraneous characters, then apply Excel’s built-in telephone number format. Formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(A1,”(“,””), “)”,””),”-“,””),” “,””),”.”,””)+0 Note that the cell appears in the middle, with function names above and substitutions below. Not only does this make the formula easier to read, it also …

Continue Reading

How to compare two text strings in Excel

by

If you need to compare two text strings in Excel to determine if they’re equal, you can use the EXACT function. Formula =EXACT(text1, text2) Explanation For example, if you want to compare A2 with B2, use: =EXACT(A2,B2) If the two strings are identical, EXACT will return TRUE. If not, EXACT will return FALSE. You can …

Continue Reading

How to display conditional message with REPT function in Excel

by

To display a conditional message, without the IF function, you can use boolean logic and the REPT function. Formula =REPT(“message”,logical test) Explanation  In the example shown, the formula in D5 (copied down) is: =REPT(“low”,C5<100) If the value in column C is less than 100, the formula returns “low”. If not, the formula returns an empty string (“”), which …

Continue Reading

How to convert numbers to text using TEXT function in Excel

by

To convert numbers into text values, you can use the TEXT function. Normally, you want to maintain numeric values in Excel, because they can be used in formulas with other numbers. However, there are situations where converting numbers to text makes sense. For example, you might want to perform a lookup on numbers using wildcards, which …

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

Convert text to numbers using VALUE function in Excel

by

To convert simple text values to numbers, you can use the the VALUE function, or simply add zero as described below. Formula =VALUE(A1) Explanation In the example shown, the formula in C5 is: =VALUE(B5) Background Sometimes Excel ends up with text in a cell, when you really want a number. There are many reasons this …

Continue Reading

Convert Numbers to Text in Excel

by

By default, numbers are right-aligned and text is left-aligned in Excel. This example teaches you how to convert numbers to ‘text strings that represent numbers’. 1. Select the range A1:A4 and change the number format  to Text. 2. Precede a number by an apostrophe and it will also be treated as text. 3a. If you add text to a number and …

Continue Reading

How to Check If A Cell Contains Specific Text in Excel

by

To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. The ISNUMBER functions is used to check for a numeric value and the  SEARCH function returns the location of a substring in a string. There’s no CONTAINS function in Excel. 1. To find the position of a substring in a text …

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

Two ways to Compare Text in Excel

by

Case-sensitive and case-insensitive ways to Compare Text in Excel. This example shows two ways to compare text in Excel. One is case-sensitive and one is case-insensitive. 1. Use the EXACT function (case-sensitive). 2. Use the formula =A1=B1 (case-insensitive).

Continue Reading

Find function vs Search function in Excel

by

The FIND function and the SEARCH function are very similar to each other. This example shows the difference. 1. To find the position of a substring in a string, use the FIND function. FIND is case-sensitive. 2. To find the position of a substring in a string, use the SEARCH function. SEARCH is case-insensitive. Note: string “excel” found at …

Continue Reading

SUBSTITUTE function vs REPLACE function in Excel

by

This example shows the difference between the SUBSTITUTE function and the REPLACE function. 1a. If you know the text to be replaced, use the SUBSTITUTE function. 1b. The SUBSTITUTE function has a 4th optional argument. You can use this argument to indicate which occurrence you want to substitute. 2. If you know the position of the text to …

Continue Reading

Complete List of Excel Text Functions References and Examples

by

Excel has many functions to offer when it comes to manipulating text strings. The Excel TEXT function returns a number in a specified number format, as text. You can use the TEXT function to embed formatted numbers inside text. Here’s a full list of Built-In Text Functions in Excel and their Descriptions below: Click each of the function link to see detailed example. …

Continue Reading

Posts navigation

  • Previous
  • 1
  • 2
  • 3
  • 4
  • 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

  • Check multiple cells are equal in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • SWITCH function example in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples

Date Time

  • How to calculate months between dates in Excel
  • How to calculate workdays per month in Excel
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Get first Monday before any date in Excel
  • How to get Holiday Date from Year in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Map inputs to arbitrary values in Excel
  • Group times into unequal buckets in Excel
  • If cell contains one of many things in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to get original number from percent change in Excel
  • 231 Keyboard Shortcut Keys In Excel
  • How to Delete Cells, Row and Rows in Excel
  • How to add sequential row numbers to a set of data in Excel
  • Excel Default Templates
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning