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.

Extracting Text

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]:

 A B C 1 This is an example

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]:

 A B C 1 This is an example

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]:

 A B C 1 This is an example

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

Combining Text

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]:

 A B C 1 First_Name Middle_Name Last_Name 2 Thomas Kevin Browne 3 Teresa Carney

Example 1:
To display the full name of the first person (Thomas), use the formula = concat (A2, “ ”, B2, “ ”, C2). A whitespace is added between the first name, middle name, and last name. The formula returns “Thomas Kevin Browne.”

Example 2:
To create an email address using the full name of the first person (Thomas) in the format of FirstName.LastName@CompanyName.com, use the formula = concat(A2, “.”, C2, “@CompanyName.com”). The formula returns “Thomas.Browne@CompanyName.com.”

Example 3:
The second person (Teresa) does not have a middle name. If the formula = concat (A3, “ ”, B3, “ ”, C3) is used, the formula returns “Teresa  Carney,” with two spaces between the first and last name. To avoid this and other similar issues, the  textjoin   function can be used.

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]:

 A B C 1 First_Name Middle_Name Last_Name 2 Thomas Kevin Browne 3 Teresa Carney

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.

Converting to Upper- and Lowercase

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]:

 A 1 Full_Name 2 Thomas Kevin Browne 3 Teresa Carney

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]:

 A 1 Full_Name 2 Thomas Kevin Browne 3 Teresa Carney

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]:

 A 1 Full_Name 2 thomas kevin browne 3 teresa carney

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

Removing Unnecessary Whitespace

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]:

 A 1 Full_Name 2 Thomas Kevin       Browne 3 Teresa    Carney

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.”

Comparing Text

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]:

 A B 1 Full_Name Name 2 thomas kevin browne Thomas Kevin Browne 3 Thomas Kevin Browne THOMAS KEVIN BROWNE 4 Thomas Kevin Browne Thomas Kevin Browne

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.

Replacing Text

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 nth 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]:

 A 1 The event is scheduled on the last day of the last week of the last month in every quarter

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.”

Converting Text Data, Numerical Data, and Datetime Data

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]:

 A 1 1233.14159 2 12/25/2019 3 0.2345

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.

Learning Excel Data Analytics with Gleim

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!

Python Lessons
Python Basics
Conditional Statements
Loops
Functions and Modules
Numerical Python (NumPy)
Pandas Basics
Pandas Data Capture and Cleansing
Merging and Grouping
Manipulating Text and Datetime Data
Visualization
Web Scraping
Errors and Exceptions