Text Functions & Formula Examples | Microsoft Excel Tutorial

Taylor Karl
Text Functions & Formula Examples | Microsoft Excel Tutorial 16495 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

  1. Functions: The PI() functionreturns the value of pi: 3.142...
  2. References: A2 returns the value in cell A2.
  3. Constants: Numbers or text values entered directly into a formula, such as 2.
  4. 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.

Empowering Teams to Make Data-Driven Decisions

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)

Change Text in Excel to Uppercase

Join text

TEXTJOIN

 

Join Text from Multiple Cells in Excel

 

extract data from a long code:

extract the beginning of a text string

LEFT

Extract Data from Left Side of Code

Extract Data from Left Side of Code

extract data from a long code: extract the middle of a text string

 

MID

Extract Middle Portion of Data in Excel

Extract Middle Portion of Text from Excel

extract data from a long code: extract the end of a text string

 

RIGHT

Extract Right Side of Data within Cell

Extract Right Side from Cell within Excel

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.

Join a String of Text Together

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

Example of Joining a String of Text in Excel

separate text into separate columns

use "Text to Columns" feature on the Data ribbon

Example of Separating Text into Columns

Combines a range of values into one cell using commas as separators

ARRAYTOTEXT

=ARRAYTOTEXT(A1:A5)

Combine a Range of Values into One Cell

Converts a number value into Thai text and includes the Thai currency Baht with the value

BAHTTEXT

=BAHTTEXT(A1)

Convert a number value into text

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

Return the corresponding character in Excel

Apple and Windows computers will have different character set.

Removes nonprintable characters

CLEAN

=CLEAN(A1)

Removes nonprintable characters

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"

Return corresponding number from character set

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

New form of Concatenate

Format a value as currency, round to the decimal place you choose, and then format the value as text.

DOLLAR

=DOLLAR(A2,2)

Format a value as currency in Excel

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)

Extract Text Function Example

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 5th character in the cell

Find Text Function Example

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

Fixed Text Function Example in Excel

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)

Return text from left side of text string in excel

Counts characters in a text string, including spaces.

LEN

=LEN(A2)

Count characters in a text string in Excel

Converts upper case letters to lower case.

LOWER

=LOWER(A2)

Convert uppercase to lowercase letters in Excel

Returns text from the middle of a larger text string, starting at a specified character count.

MID

=MID(A2,6,5)

Return text from the middle of a large text string in Excel

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)

Return a number that was originally formatted as text as a value

Converts text into proper case, using upper case letters to begin each word.

PROPER

=PROPER(A2)

Convert text into a proper case in Excel

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.

Replace Text Example in Excel

Repeats a text string a specified number of times.

REPT

=REPT(A2,3)

Rept Text Example in Excel

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)

Return text from right side of a text string in Excel

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)

Return a character count for a text string in Excel

Find text in a text string and replace it with new text.

SUBSTITUTE

=SUBSTITUTE(A2,"Doe",B2)

Substitute Text in Excel Example

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)

Test Whether Cell is a Text Value

Format a number value using the specified format in the second argument which requires custom formatting codes.

TEXT

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

Text Function Example in Excel

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

Textafter Function Example in Excel

New function in 365 and will return all the text from the beginning of a text string before a specified delimiter.

TEXTBEFORE

=TEXTBEFORE(A2," ")

Textbefore Example in Excel

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)

Textjoin Example in Excel

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.

Textsplit Example in Excel

Remove spaces from the left or right of a text string.

TRIM

=TRIM(A2)

Trim Example in Excel

Return the Unicode character that corresponds to a chosen number. Not all numbers have corresponding characters.

UNICHAR

=UNICHAR(A2)

Unichar Example in Excel

Returns the opposite of UNICHAR and provides the Unicode number for a chosen character.

UNICODE

=UNICODE(A2)

Unicode Example in Excel

Change the case of a text string to uppercase letters.

UPPER

=UPPER(A2)

Change the case of a text string to uppercase in Excel

Convert values formatted as text to a number value. This allows other functions like SUM to use the values.

VALUE

=VALUE(B2)

Convert Values Formatted as Text in Excel

Convert a number to a value formatted as text.

VALUETOTEXT

=VALUETOTEXT(A2)

Convert a Number to a Text Value in Excel

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.

Print