Text Functions & Formula Examples | Microsoft Excel Tutorial

Taylor Karl / Wednesday, March 22, 2023

/ Categories: Resources, Microsoft Office, Training Trends

Text Functions & Formula Examples | Microsoft Excel Tutorial

19242 0

Why create Excel Formula for Text

Excel is a spreadsheet program within the Microsoft Office suite. At the most basic level, information is entered by the user into rows and columns to organize the data. However, with a bit of knowledge of text functions and formulas, Excel turns into a powerful tool that enables you to:

manipulate text into various formats,
include text with other formats such as numbers and dates, and
extract and then combine parts of a text string with other data.

Microsoft Excel components for formulas and functions

To get started, it’s important that you know your way around Excel. It is part of Microsoft Office, but as a spreadsheet program, has components unlike Word or PowerPoint. In Microsoft Excel, you have spreadsheets that are organized into columns and rows with data entered into specific cells.

Ribbon: toolbars located at the top of all Office program windows
Spreadsheet: also called a "worksheet," is a page in which boxes (called "cells") are created through the intersection of columns and rows in a grid. Numerical and text data is entered into cells. Each cell can hold one value (numerical or text) or a formula that does an automatic calculation of a combination of other designated cells.
Column: vertical lines of the spreadsheet, lettered to aid in navigation of the sheet and the creation of formulas to calculate values. In one spreadsheet, columns begin at letter A and continue to XFD.
Row: horizontal lines of the spreadsheet, numbered to aid in navigation of the sheet and the creation of formulas to calculate values. In one spreadsheet, rows start at number 1 and continue to 1,048,576.
Cell: The boxes created by the columns and rows intersecting in the spreadsheet. Cells are the building blocks of the spreadsheet. The cells within a spreadsheet form a grid (just like graph paper). Every cell has a designation of column letter and row number. Every cell can be identified as (column, row) with the column being listed first.

In this image, you see the letters of the Columns (top, left to right, A – E), and the numbers of the Rows (far left, top to bottom, 1 – 10). The cells are created with the intersection of columns and rows. In the example, the cell highlighted is cell (B, 2) as it is Column B and Row 2.

Columns and Rows in Excel

There are 65,490 possible unique cell formats and cell styles! Excel is an amazing tool to create reports, to set up automatic formulas to streamline monitoring and reporting, and to use system functions to better analyze data. But only if you know how to use it. To maximize Excel, you need to know these underlying terms and definitions:

Function: a predefined formula using designated cells and values to complete a specific calculation; functions are listed on the Formula tab on the Ribbon.
Formula: a formula is an expression that operates on values in a range of cells and returns a result (which can be an error); use to perform calculations such as addition, subtraction, multiplication, and division; ALWAYS start any function or formula with the "=", all formulas contains any or all of these 4 components:

From Microsoft Support

Parts of a formula

Functions: The PI() functionreturns the value of pi: 3.142...
References: A2 returns the value in cell A2.
Constants: Numbers or text values entered directly into a formula, such as 2.
Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.

How do you use Text Formulas in Excel

Text formulas and text functions are used as all formulas and functions are in Excel to:

save time,
automate standard calculations,
ensure consistency and accuracy in calculations, and
create standard reports efficiently.

Submit your email below to download our free eBook, Empowering Teams to Make Data-Driven Decisions

Excel Formula for Text

Compared to numerical Excel functions which can become quite complex, the TEXT formula is very straightforward. If you do receive an ERROR message as a result of your Text formula, first check the format code to ensure everything is in place.

The TEXT function returns the #NAME? error if you omit the quotation marks around the format code.

For example, the formula:

=TEXT(A2, mm/dd/yy)

is incorrect and should be written this way:

=TEXT(A2, "mm/dd/yy")

to ensure the desired result.

Text Formula Example

Note for the text function, as for any function, you start the cell line with the "=" to signal to Excel that instead of data (being a date, number, or text) that a calculation is needed.

Frequently Used Excel Formula for Text Functions and Formulas

Common Excel text formulas are provided below. Be careful how you format your functions (always start the cell with the "=" to signal that a calculation is needed).

Function Name / Description	Function	Example
Change text case	LOWER, PROPER, or UPPER	last name is in proper case and we would like to change it all to upper case =UPPER(A2)
Join text	TEXTJOIN
extract data from a long code: extract the beginning of a text string	LEFT
extract data from a long code: extract the middle of a text string	MID
extract data from a long code: extract the end of a text string	RIGHT
join a string of text together with a delimiter (how selected text is joined, for example, with a space or with a comma)	" "	When combining text into a single text string, Excel will not add a space between text automatically. When you need to extract the middle of a text string, and the length of the text varies, you can use both MID and FIND
separate text into separate columns	use "Text to Columns" feature on the Data ribbon
Combines a range of values into one cell using commas as separators	ARRAYTOTEXT	=ARRAYTOTEXT(A1:A5)
Converts a number value into Thai text and includes the Thai currency Baht with the value	BAHTTEXT	=BAHTTEXT(A1)
Returns the corresponding character from your computer’s character set.	CHAR	=A2&CHAR(10)&B2 – 10 represents a new line character on a Windows computer Apple and Windows computers will have different character set.
Removes nonprintable characters	CLEAN	=CLEAN(A1)
Returns the corresponding number in your computer’s character set for the first character in a reference	CODE	=CODE(A1) – this will return the number corresponding to "J"
New form of CONCATENATE; joins separate text values into one text value within a cell. The text values may be a single cell reference, a range of cells, or a string of text that you enter. Text strings must be enclosed within "quotation marks".	CONCAT	=CONCAT(A2," ",B2) – the " " represents a space added as the second piece of text to be joined together
Format a value as currency, round to the decimal place you choose, and then format the value as text.	DOLLAR	=DOLLAR(A2,2) Not the ideal option for formatting as currency, since a value formatted as text cannot be calculated by many functions such as SUM
Compares 2 values and returns TRUE is they are the same, or FALSE if there is any difference. The function only compares the content of the cell, not formatting.	EXACT	=EXACT(A2,B2)
Locates and returns the position of a text string within a larger text string	FIND	=FIND(" ",A2) – The " " is referring to a space as the text to search for. In this example the space is the 5^th character in the cell
Converts a number value into a formatted value with specified amount of decimal places and commas. Then the value is left formatted as text. Usually this method of formatting would not be preferred since many functions would not be able to calculate a number formatted as text.	FIXED	=FIXED(A2,1,)
Returns text starting from the left side of a text string. It will return the first character unless you choose a greater number of characters in the second argument.	LEFT	=LEFT(A2,4)
Counts characters in a text string, including spaces.	LEN	=LEN(A2)
Converts upper case letters to lower case.	LOWER	=LOWER(A2)
Returns text from the middle of a larger text string, starting at a specified character count.	MID	=MID(A2,6,5)
Returns a number that was originally formatted as text as a value formatted as a number. If the values have characters other than commas or periods to separate groups (like thousands) or decimals, enter those characters as arguments.	NUMBERVALUE	=NUMBERVALUE(A2)
Converts text into proper case, using upper case letters to begin each word.	PROPER	=PROPER(A2)
Can replace a piece of a text string with new text, starting at the point you specify in the cell.	REPLACE	=REPLACE(A2,6,3,B2) The arguments include the starting point of the text to replace, 6, and how many characters to remove from the old text, 3.
Repeats a text string a specified number of times.	REPT	=REPT(A2,3)
Returns text from the right side of a text string. It will return the last character unless a specified number of characters is entered.	RIGHT	=RIGHT(A2,5)
Returns a character count for a text string within a larger text string. The count will begin with the left most character in the cell, unless another point is specified.	SEARCH	=SEARCH(" ",A2)
Find text in a text string and replace it with new text.	SUBSTITUTE	=SUBSTITUTE(A2,"Doe",B2)
Test a cell to check if it is a text value or not. If the cell is text, the function will return a copy of the text. If there is no text in the cell the function will return a blank cell.	T	=T(A2)
Format a number value using the specified format in the second argument which requires custom formatting codes.	TEXT	=TEXT(A2,"mm/dd/yy")
New function in 365 and will return all the text from a text string after a specified delimiter.	TEXTAFTER	=TEXTAFTER(A2," ") The " " refers to a space entered in the original text string
New function in 365 and will return all the text from the beginning of a text string before a specified delimiter.	TEXTBEFORE	=TEXTBEFORE(A2," ")
New function in 365 and will return concatenated text strings. The arguments will require a delimiter, or a character to be inserted between the text strings, and a true or false argument to skip blank cells or include blanks.	TEXTJOIN	=TEXTJOIN(" ",TRUE,A2:C2)
New function in 365 and will do the opposite of TEXTJOIN. It will use a delimiter to split text strings into multiple columns or rows of values.	TEXTSPLIT	=TEXTSPLIT(A2," ") – The " " is referring to a space as the delimiter. The function will use each instance of the delimiter to separate text into columns.
Remove spaces from the left or right of a text string.	TRIM	=TRIM(A2)
Return the Unicode character that corresponds to a chosen number. Not all numbers have corresponding characters.	UNICHAR	=UNICHAR(A2)
Returns the opposite of UNICHAR and provides the Unicode number for a chosen character.	UNICODE	=UNICODE(A2)
Change the case of a text string to uppercase letters.	UPPER	=UPPER(A2)
Convert values formatted as text to a number value. This allows other functions like SUM to use the values.	VALUE	=VALUE(B2)
Convert a number to a value formatted as text.	VALUETOTEXT	=VALUETOTEXT(A2)

Tips for Written Text Formulas and Text Functions

Spreadsheets are very powerful tools for data collection and reporting. However, they are susceptible to user error so make sure that you use these tips:

Include spaces when adding multiple cells in a text string.
Start any function or formula with the "="
When selecting a range to use in any formula/functions, make sure to include a ":" between the first and last cell to prevent the #NULL! Error

Null Text Error in Excel

Use the small icon to the left of the error message for options for resolving an error message

Text Extraction Functions in Excel

And no matter what you are creating in a Microsoft Excel spreadsheet, always take advantage of the HELP ribbon to access to Microsoft’s help database. It is almost guaranteed that your question or issue is one that others have experienced so there will be help in the database.

Screenshot of Help Ribbon in Excel

Summary

Use the Microsoft Excel text function to streamline and automate reporting for your business needs. Asking questions and using learning resources is a key part of how you can not only learn Excel but also learn how to maximize its features.