LEFT function in Excel

Excel TEXT functions return a number in a specified number format

How to split text string at specific character in Excel

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… read more »

How to get Excel workbook path only

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… read more »

Get workbook name and path without sheet in Excel

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… read more »

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… read more »

How to extract name from email address in Excel

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:… read more »

How to create email address with name and domain in Excel

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… read more »

How to create email address from name in Excel

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… read more »

How to extract domain name from URL in Excel

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… read more »

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

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… read more »

Get first name from full name — Manipulating NAMES in Excel

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)… read more »

Sidebar