Excel Functions: Working with Text Data

Text data do not contain mathematical or statistical values. Insights from text data are drawn from the combination or decomposition of text strings that convey meaningful information. This blog introduces some common text functions that deal with text data.

  left   function

The   left   function extracts the leftmost characters (based on the number of characters specified) in a text string. It has the following syntax:

= left(text,[num_chars])
Note: Whitespace is treated as a character.

Assume the following dataset [you may paste it into Excel]:

Examples:
The formula = left(B1,4) extracts the leftmost 4 characters from the text in cell B1 and returns the text “This.”

The formula = left(B1,6) extracts the leftmost 6 characters from the text in cell B1 and returns the text “This i.” The whitespace between “this” and “is” is the 5th character.

  right function 

The  right   function extracts the rightmost characters (based on the number of characters specified) in a text string. It has the following syntax:

= right(text,[num_chars])
Note: Whitespace is treated as a character.

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula = right(B1,7) extracts the rightmost 7 characters from the text in cell B1 and returns the text “example.”

The formula = right(B1,9) extracts the rightmost 9 characters from the text in cell B1 and returns the text “n example.” The whitespace between “n” and “example” is the 8th character.

  mid   function

The  mid  function extracts characters from the middle of a text string (based on the specified starting character and the number of characters to be extracted). It has the following syntax:

= mid(text, start_num, num_chars)
Note: Whitespace is treated as a character.

Assume the following dataset [you may paste it into Excel]:

Example:
To extract the text “is” (which begins with the 6th character and has a length of 2 characters), use the formula = mid(B1, 6, 2).

  concat   function

The  concat   function combines text data. It has the following syntax:

= concat(text1, [text2], …)

Note: In Excel 2016 and later versions, the  concat   function replaces the previous  concatenate   function. Concatenate can still be used for backwards compatibility, but MicrosoftOpens in new window recommends users begin use  concat   to avoid issues with future versions of Excel.

Assume the following dataset [you may paste it into Excel]:

  textjoin   function [available in Excel 2019 or later versions]

The  textjoin   function combines several text strings using a specified delimiter (a character that separates text strings) and specifying whether to ignore empty arguments. It has the following syntax:

= textjoin(delimiter, ignore_empty, text1, [text2], …)

Assume the following dataset [you may paste it into Excel]:

Example:
To find the full name of the second person (Teresa), use the formula = textjoin(“ ”, TRUE, A3:C3). The delimiter is specified as a whitespace and empty arguments (the middle name in cell B3) in the range A3 through C3 are ignored. The formula returns “Teresa Carney” with only one whitespace between the first and last name.

  upper   function

The  upper   function converts all the characters of a text string to uppercase. It has the following syntax:

= upper(text)

Assume the following dataset [you may paste it into Excel]:

Example:
The formula = upper(A2) returns the text “THOMAS KEVIN BROWNE.”

  lower   function

The  lower   function converts all the characters of a text string to lowercase. It has the following syntax:

= lower(text)

Assume the following dataset [you may paste it into Excel]:

Example:
The formula = lower(A2) returns the text “thomas kevin browne.”

  proper   function

The  proper   function capitalizes the first character of each word in a text string. It has the following syntax:

= proper(text)

Assume the following dataset [you may paste it into Excel]:

Example:
The formula = proper(A2) returns the text “Thomas Kevin Browne.”

  trim   function

The  trim   function removes all unnecessary whitespace in a text string. Unnecessary whitespace includes any whitespace at the very beginning or very end of the string. It has the following syntax:

= trim(text)

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula = trim(A1) returns the string without the extra whitespace in between the words. It returns the text “Thomas Kevin Browne.”

Example 2:
The formula = trim(“ Thomas Kevin Browne ”) keeps only one whitespace between the words in this text string and removes (1) the whitespace before the first name, (2) the multiple spaces between the middle and last name, and (3) the whitespace after the last name. It returns the text “Thomas Kevin Browne.”

  exact   function

The  exact   function compares two text strings and returns TRUE when they are identical. It has the following syntax:

= exact(text1, text2)

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula = exact(A2, B2) returns FALSE because the first characters of the names in A2 are not capitalized.

Example 2:
The formula = exact(A3, B3) returns FALSE because not all the characters of the names in A3 are capitalized.

Example 3:
The formula = exact(A4, B4) returns FALSE because of the difference in the number of whitespace.

  substitute   function

The  substitute   function substitutes a text string with another text string. It has the following syntax:

= substitute(text, old_text, new_text, [instance_num])
Note: The instance number specifies the n^th instance of the old text to be substituted. Leaving this blank substitutes all instances of the old text.

Assume the following dataset [you may paste it into Excel]:

Example 1:
To change the word “last” to “first,” use the formula = substitute(A1, “last”, “first”). The formula replaces all instances of the text “last” in cell A1 with the text “first.” It returns the text “The event is scheduled on the first day of the first week of the first month in every quarter.”

Example 2:
The formula = substitute(A1, “last”, “first”, 3) replaces the third instance of the text “last” with the text “first”. It returns the text “The event is scheduled on the last day of the last week of the first month in every quarter.”

  text   function

The  text   function converts a numerical or datetime entry to text in the specified format. It has the following syntax:

= text(value,format_text)

Assume the following dataset [you may paste it into Excel]:

Example 1:
The formula = text(A1, “#,#0.000”) converts the number in cell A1 to text in a format that uses commas to denote thousands, millions, etc., and rounds the number to 3 decimal places.

Thus, the formula returns the text “1,233.142.”

Example 2:
The formula = text(A2, “MM-DD-YY”) converts the datetime value in cell A2 to text in a format of MM/DD/YY and separates the day, month, and year using a dash (-). Thus, the formula returns the text “12-25-19.”

Example 3:
The formula = text(A2, “DDD”) converts the datetime value in cell A2 to text that displays the day of the week (short form). It returns the text “Wed.”

Example 4:
The formula = text(A3, “0.000%”) converts the decimal number in cell A3 to text in the format of a percentage rounded to 3 decimal places. It returns the text “23.450%.”

You can find a complete list of formats and guidance on how to use them on Microsoft’s website Opens in new window.

  value   function

The  value   function converts text to a numerical value. It has the following syntax:

= value(text)

Example:
The formula = value(“3.141592”) converts the text “3.141592” to the number 3.141592.

At Gleim, we know learning data analytics is vital for future accounting professionals. That is why we are offering these series of data analytics blogs and continually updating all of our CPA, CMA, and CIA Review materials with the necessary information you need to pass your exams.

We’ll continue our weekly blog series. Check back regularly for exam news and study tips!

Excel Lessons
Excel Basics
Excel Shortcuts
Excel Calculation Rules
Cell References
Excel Functions
Function Basics
Working with Numbers
Working with Datetime Data
Working with Text Data
Logical Testing
Summarizing Data
Filtering and Sorting
Lookup Functions
Tables
Pivot Tables