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

Data Analysis

  • Move chart to a separate worksheet in Excel
  • How To Create Pareto Chart in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Filter Data Based on Date in Excel
  • Reverse List in Excel

References

  • Create hyperlink with VLOOKUP in Excel
  • How to get last row in mixed data with blanks in Excel
  • How to get last row in numeric data in Excel
  • Basic INDEX MATCH approximate in Excel
  • How to use Excel LOOKUP Function

Data Validations

  • Excel Data validation unique values only
  • Excel Data validation no punctuation
  • Excel Data validation allow weekday only
  • Excel Data validation number multiple 100
  • Data validation must not exist in list

Tag: LEFT function

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

How to split text string at specific character in Excel

by

To split a text string at a certain character, you can use a combination of the LEFT, RIGHT, LEN, and FIND functions. Formula =LEFT(text,FIND(character,text)-1) Explanation In the example shown, the formula in C5 is: =LEFT(B5,FIND(“_”,B5)-1) And the formula in D5 is: =RIGHT(B5,LEN(B5)-FIND(“_”,B5)) How these formulas work The first formula uses the FIND function to locate …

Continue Reading

How to get Excel workbook path only

by

If you want to get the workbook path (directory) only, you can do so with a formula that uses the LEFT and the FIND function. The result will look something like this: Drive:\path\to\file\ //Windows Drive:path:to:file: //Mac Formula =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full file name and …

Continue Reading

Split dimensions into two parts in Excel Worksheet

by

If you have worksheet that contains text dimensions (i.e. “50 ft x 200 ft” etc.) you can split the into two parts with formulas that use several text functions. Explanation Background A common annoyance with data is that it may be represented as text instead of numbers. This is especially common with dimensions, which may …

Continue Reading

Split numbers from units of measure in Excel

by

To split a number from a unit value, you need to determine the position of the last number. If you add 1 to that position, you have the start of the unit text. Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested …

Continue Reading

Get workbook name and path without sheet in Excel

by

If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this: path[workbook.xlsm] Formula =SUBSTITUTE( LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””) Explanation How the formula works The CELL …

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

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

Get date from day number in Excel

by

This tutorial show how to get date from day number in Excel using the example below. To get a real date from day number, or “nth day of year” you can use the DATE function. Formula =DATE(year,1,daynum) Explanation of how this formula works In the example shown, the formula in C5 is: =DATE(2015,1,B5) The DATE function build …

Continue Reading

Convert text date dd/mm/yy to mm/dd/yy in Excel

by

This tutorial shows how to Convert text date dd/mm/yy to mm/dd/yy in Excel using example below. To convert dates in text format dd/mm/yy to a true date in mm/dd/yy format, you can use uses a formula based on the DATE function. Formula =DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2)) Explanation In the example shown, the formula in C5 is: =DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2)) Which …

Continue Reading

Convert date string to date time in Excel

by

This tutorial shows how to Convert date string to date time in Excel using example below. To convert a date string to a datetime (date with time) you can parse the text into separate components then build a proper datetime. When date information from other systems is pasted or imported to Excel, it may not …

Continue Reading

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

Convert text to date in Excel

by

This tutorial show how to Convert text to date in Excel using the example below. To convert text in an unrecognized date format to a proper Excel date, you can parse the text and assemble a proper date with a formula based on several functions: DATE, LEFT, MID, and RIGHT. Note: Before you go the formula route, see …

Continue Reading

Excel Data validation must begin with

by

Using the example below, this tutorial shows how to create Data validation must begin with in Excel. Formula =EXACT(LEFT(A1,3),”XX-“) Explanation To allow only values that begin with certain text, you can use data validation with a custom formula based on the EXACT and LEFT functions. In the example shown, the data validation applied to C5:C9 is: =EXACT(LEFT(C5,3),”MX-“) …

Continue Reading

Count numbers that begin with in Excel

by

This tutorial shows how to Count numbers that begin with in Excel using the example below; Formula =SUMPRODUCT(–(LEFT(range,chars)=”xx”)) Explanation To count numbers in a range that begin with specific numbers, you can use a formula based on the SUMPRODUCT function and LEFT functions. In the example shown, the formula in E6 is: =SUMPRODUCT(–(LEFT(B5:B11,2)=”25″)) How this …

Continue Reading

Count Number of Words in Excel

by

This chapter shows how to count the number of words in a cell. 1a. The TRIM function returns a string with leading spaces, extra spaces and trailing spaces removed. 1b. To get the length of the string with normal spaces, we combine the LEN and TRIM function. 2a. The SUBSTITUTE function replaces existing text with new text in a …

Continue Reading

How to calculate Number of Instances in Excel

by

This example describes how to count the number of instances of text (or a number) in a cell. 1. Use the LEN function to get the length of the string (25 characters, including spaces). 2. The SUBSTITUTE function replaces existing text with new text in a string. LEN(SUBSTITUTE(A1,B1,””)) equals 13 (the length of the string without …

Continue Reading

How to Separate Text Strings in Excel

by

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 …

Continue Reading

LEFT, LEFTB functions: Description, Usage, Syntax, Examples and Explanation

by

What are LEFT, LEFTB functions in Excel? LEFT, LEFTB functions are  TEXT functions in Microsoft Excel. LEFT returns the first character or characters in a text string, based on the number of characters you specify. While, LEFTB returns the first character or characters in a text string, based on the number of bytes you specify. Important: …

Continue Reading

Convert feet and inches to inches in Excel

by

To convert a measurement in feet and inches to inches only (i.e. 4’5″ to 53) you can use a formula based on several functions, including LEFT, FIND, MID, and SUBSTITUTE. See illustration below: Formula =LEFT(B5,FIND(“‘”,B5)-1)*12+SUBSTITUTE (MID(B5,FIND(“‘”,B5)+1,LEN(B5)),””””,””) Explanation In the example shown, the formula in D5 is: =LEFT(B5,FIND(“‘”,B5)-1)*12+SUBSTITUTE (MID(B5,FIND(“‘”,B5)+1,LEN(B5)),””””,””) How this formula works In the first …

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

Remove unwanted characters in Excel

by

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 …

Continue Reading

Remove file extension from filename in Excel

by

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 …

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

How to get last word in a cell in Excel

by

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 …

Continue Reading

How to get first word in Excel

by

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 …

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

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

by

If you need extract the last name from a full name in LAST, FIRST format, you can do so with a formula that uses the LEFT and FIND functions. The formula works with names in this format, where a comma and space separate the last name from the first name. Note: this formula will only …

Continue Reading

Get first name from full name — Manipulating NAMES in Excel

by

If you need extract the first name from a full name, you can easily do so with the FIND and LEFT functions. In the  formula below, name is a full name, with a space separating the first name from other parts of the name. Note: this formula does not account for titles (Ms., Mr., etc) …

Continue Reading

How to extract substring in Excel

by

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 …

Continue Reading

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

  • IF function: Description, Usage, Syntax, Examples and Explanation
  • OR function Examples in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • How to Calculate Age in Excel
  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Convert text to date in Excel
  • Get week number from date in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Group times into unequal buckets in Excel
  • How to randomly assign people to groups in Excel
  • Group arbitrary text values in Excel
  • Running count group by n size in Excel

General

  • How to Insert Cells, Row and Rows in Excel
  • How to count total columns in range in Excel
  • Find Most Frequently Occurring Word in Excel Worksheet
  • Excel Operators
  • Cell References: Relative, Absolute and Mixed Referencing Examples
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning