Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Tag: SUBSTITUTE function

SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation

 What is SUBSTITUTE function in Excel? SUBSTITUTE function is one of TEXT functions in Microsoft Excel that substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string. Syntax of SUBSTITUTE function…

Convert feet and inches to inches in Excel

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 part of the formula, feet are…

Convert column number to letter in Excel

To convert a column number to an Excel column letter (e.g. A, B, C, etc.) you can use a formula based on the ADDRESS and SUBSTITUTE functions. See example below: Formula =SUBSTITUTE(ADDRESS(1,col_number,4),”1″,””) Explanation In the example shown, the formula in C5 is: =SUBSTITUTE(ADDRESS(1,B5,4),”1″,””) The first step is to construct an address that contains the column number. We do this with…

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…

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 function is used with “filename”…

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…

Split dimensions into two parts in Excel Worksheet

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 appear in one text string…

Remove unwanted characters in Excel

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 find and replace text in…

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…

Replace one character with another in Excel

To replace or substitute all occurrences of one character with another character, you can use the SUBSTITUTE function. Formula =SUBSTITUTE(ref,old,new) Explanation In the example shown, the formula in C6 is: =SUBSTITUTE(B6,” “,”-“) How this formula works The SUBSTITUTE function is full automatic. All you need to do is supply “old text” and “new text”. SUBSTITUTE will replace every instance of…

Remove text by matching in a cell in Excel

To remove text from a cell based by matching content (not location), you can use the SUBSTITUTE function. Formula =SUBSTITUTE(B6,text_to_remove,””) Explanation In the example shown, the formula in C6 is: =SUBSTITUTE(B6,”-“,””) How this formula works The SUBSTITUTE function lets you replace text by matching content. In this case, we want to remove hyphens from telephone numbers. The SUBSTITUTE function can handle…

Remove line breaks in a cell in Excel

To remove line breaks from a cell, or from text inside a formula, you can use a formula based on the SUBSTITUTE and CHAR functions. Formula =SUBSTITUTE(A1,CHAR(10),”, “) Explanation In the example shown, the formula in C5 is: =SUBSTITUTE(B5,CHAR(10),”, “) which replaces line breaks in B5 with commas. How this formula works First, you should know that Excel contains two…

Get position of 2nd 3rd and more instance of character in Excel

To get the position of the 2nd, 3rd, 4th, etc. instance of a specific character inside a text string, you can use the FIND and SUBSTITUTE functions. Note: we use “~” in this case only because it rarely occurs in other text. You can use any character that you know won’t appear in the text. Formula =FIND(“~”,SUBSTITUTE(text,char,”~”,instance)) Explanation In the…

Join cells with comma in Excel

To join multiple cell values with a comma, you can use a formula based on the SUBSTITUTE and TRIM functions. You can use this same approach to concatenate values in cells with any delimiter you like. Formula =SUBSTITUTE(TRIM(A1&” “&B1&” “&C1&” “&D1&” “&E1),” “,”, “) Explanation In the example shown, the formula in G5 is: =SUBSTITUTE(TRIM(B5&” “&C5&” “&D5&” “&E5&” “&F5),” “,”, “) Working from the…

How to get last word in a cell in Excel

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 of a “brute force” approach…

How to find and replace multiple values at same time in Excel

To find and replace multiple values with a formula, you can nest multiple SUBSTITUTE functions together, and feed in find/replace pairs from another table using the INDEX function. Formula =SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)), INDEX(find,2),INDEX(replace,2)) Explanation In the example shown, we are performing 4 separate find and replace operations. The formula in G5 is: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1), INDEX(replace,1)),INDEX(find,2),INDEX(replace,2)), INDEX(find,3),INDEX(replace,3)),INDEX(find,4),INDEX(replace,4)) where “find” is the named range E5:E8, and “replace” is…

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 find nth occurrence of character in Excel

To find the nth occurrence of a character in a text string, you can use a formula based on the FIND and SUBSTITUTE functions. Formula =FIND(CHAR(160),SUBSTITUTE (text,”@”,CHAR(160),N)) Explanation In the example shown, the formula in D5 is: =FIND(CHAR(160),SUBSTITUTE (B5,”@”,CHAR(160),C5)) How this formula works In this example we are looking for the nth occurrence of the “@” character. Working from the…

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…

How to get last line in cell in Excel

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(B5,CHAR(10),REPT(” “,200)),200)) Note: 200 is an arbitrary number that represents the longest line you expect to find in a cell. If you have longer lines, increase this number as needed. Explanation In the example shown, the formula…

How to count specific characters in a range in Excel

If you need to count specific characters in a range of cells, you can do so with a formula that uses LEN and SUBSTITUTE, along with the SUMPRODUCT function. Formula =SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range,text,””))) Explanation In the example, the active cell contains this formula: =SUMPRODUCT(LEN(B3:B7)-LEN(SUBSTITUTE(B3:B7,”o”,””))) How the formula works For each cell in the range, SUBSTITUTE removes all the o’s from the text,…

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

Convert text to numbers using VALUE function in Excel

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 might happen, and many ways…

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…

SUBSTITUTE function vs REPLACE function in Excel

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 be replaced, use the REPLACE…

How to calculate Number of Instances in Excel

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 the words dog). If we…

Count Number of Words in Excel

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 text string. We use the…

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

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 the cell ranges. Visit: Cell References:…