14

Jun

2021

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.

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.

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.

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

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 Microsoft*Opens 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.

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.

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

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

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

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

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.

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

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

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

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

Filtering and Sorting

Lookup Functions

Tables

Pivot Tables

**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