This function is really helpful when it comes to converting text-formatted values representing the numbers into numbers that can be used in other Excel formulas and calculations. VALUE(text) - converts a text string to a number. Converting text strings with custom delimiters to dates.If omitted, then every occurrence of the old text will be replaced with the new text.įor example, the following SUBSTITUTE formula replaces all commas in cell A1 with semicolons: Nth_appearance - an optional parameter that specifies which occurrence of old_text you want to replace with new_text.New_text - the characters you want to replace the old text with.Old_text - the characters you want to replace.Text - the original text string or reference to a cell where you want to substitute certain characters.The syntax of the SUBSTITUTE function is as follows: SUBSTITUTE(text, old_text, new_text, ) replaces one set of characters with another in a specified cell or a text string. The following screenshot demonstrates an example of usage:įor more formula examples, see 3 ways to remove spaces between words / numbers in Excel.

Where text is either a text string or reference to the cell containing the text from which you want to remove spaces. TRIM(text) removes leading, trailing spaces as well as excess spaces between words. You can find plenty more formula examples in the tutorial CONCATENATE in Excel: combine text strings, cells and columns. An analogues result can be achieved by using the Excel & operator, as demonstrated in the following screenshot. TEXT formulas to convert a number to textĬONCATENATE(text1,, …) is designed to join several pieces of text together or combine values from several cells into a single cell.TEXT function to convert a date to text format.=TEXT(A1,'€#,#0.00') - converts a number in A1 into a currency text string such as '€3.00'. =TEXT(A1,'mm/dd/yyyy') - convert a date in cell A1 into a text string in the traditional US date format, such as '' (month/day/year). The following formulas demonstrate the Excel TEXT function in action: Value is a numeric value you want to convert to text.TEXT(value, format_text) is used to convert a number or a date into a test string in the specified format, where: Here are the most essential ones: TEXT function There exist a great lot of Microsoft Excel functions to manipulate text strings. UDF functions to count and sum cells by color.Select a category to go to the functions list with short descriptions and links to examples of use. These are VBA-based functions that extend the basics of Excel with custom functionality. The last category includes custom Excel functions, aka user-defined functions (UDF). Most of the functions listed below are Excel worksheet functions that are used as part of formulas in a cell. On this page, you will find the most useful and most used Excel functions, which we've already covered in depth in individual step-by-step guides. In this tutorial, we do not aim to explorer all and each function available in Microsoft Excel. Some of them are utilized by many Excel users on a daily basis while others are so very specific that only a small group of financial, statistical or engineering specialists understand and need them. It lists the most essential text, math, statistical, logical and other functions along with formula examples and links to related resources.Įxcel provides an enormous number of functions to analyze, audit and calculate data. This page can be your entry point to learning Microsoft Excel functions.

Top 10 microsoft excel functions