Text Functions

 

Text Formula Basics

If you have made it this far, you know that any time you use text in a formula, you must surround the text with quotations.

1. Create a formula that outputs “text”

(Hint)

Save Question to Custom Practice Set?

The ampersand (&) allows you to merge separate strings of text.

2. Combine these two strings of text “te” and “xt”

(Hint)

Save Question to Custom Practice Set?

You can also combine a string (or strings) of text with a cell value.

3. Create a formula in Cell D4 to combine “Q” with the quarter number found in C4 to output “Q2”

(Hint)

Save Question to Custom Practice Set?

Numbers Stored as Text

Numerical values can be “stored as text”. Numbers stored as text can cause serious issues if not handled properly; a number stored as text will not equal it's corresponding numerical counterpart. In other words “123” will not equal 123. This can result in formula errors or other undesired results.

You might be wondering why anyone would store a number as text. You would rarely want to intentionally store numbers of text, but sometimes numbers stored as text will be brought in when importing external data into Excel. The easiest way to tell if a number is stored as text is to look at the cell alignment. Usually, text is left-aligned and numbers are right-aligned. If you see a number that is left aligned it is very likely that it is stored as text. You can also tell if a number is stored as text by checking if the cell formula start with an apostrophe ('). Excel hides leading apostrophes when displaying the cell result, but you can see the leading apostrophe when looking at the actual cell formula.

’003231

Another method is to check the number format of the cell:

Important! In the image above, you can see that leading zeroes are kept when storing numbers as text. Occasionally, this can be useful.

4. Store the number 0123 as text by using a leading apostrophe.

(Hint)

Save Question to Custom Practice Set?

One option to to convert a number stored as text to a number is Text to Columns feature. This is good for converting large ranges of numbers stored as text into numbers, but it is not a formula so we will not cover it this tutorial.

Or you can use the VALUE Function:

=value(b1)

5. Convert the number stored as text from C4 to a number.

(Hint)

Save Question to Custom Practice Set?

MID, LEFT, and RIGHT

The MID, LEFT, and RIGHT functions return a portion of a string:

Function	Description
Left	Returns a number of characters starting from the left
Right	Returns a number of characters starting from the right
Mid	Returns a number of characters starting from a given starting position

The syntax for these functions is simple. Here are some examples:

6. Extract the right two characters of the string of text.

(Hint)

Save Question to Custom Practice Set?

7. Extract the middle two characters from the string of text.

(Hint)

Save Question to Custom Practice Set?

8. Extract all the text starting at position 2 (hint: use 999 for the num_chars to return all remaining characters.)

(Hint)

Save Question to Custom Practice Set?

FIND Function

The FIND Function returns the starting position of one text string within another text string. Note: The FIND Function is case-sensitive: “This” <> “this”. We will look at the Find Function Syntax:

• find_text- The text to search for
• within_text- The text to search in
• [start_num] - OPTIONAL - The character number to begin the search. Note: the formula result will always display the character position relative to the start of the text and not the start_num. The start_num is only used to skip a certain number of characters before beginning the search allowing you to find the second, third, etc. instance of a string.

=FIND(find_text,within_text,[start_num])

9. Use FIND to find the position of the comma “,” in the cell below.

(Hint)

Save Question to Custom Practice Set?

SUBSTITUTE Function

The SUBSTITUTE Function searches for a string of text, replacing it with a different string of text.

SUBSTITUTE Syntax

• text The text to search in
• old_text- The text to search for and replace
• new_text - The replacement text
• [instance_num]- OPTIONAL - The instance number to replace. If left blank, all instances will be replaced.

.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

The SUBSTITUTE Function is useful when cleaning data. By substituting the old_text with a blank string (“”), you can effectly remove the old_text.

10. Use SUBSTITUTE to remove all of the “-“ from the SSN below.

(Hint)

Save Question to Custom Practice Set?

TRIM Function

The TRIM Function removes extra spaces from a string of text. It removes leading spaces, trailing spaces, and any consecutive spaces so that there is never more than one space in a row. The only input required is the text that you want to trim.

=TRIM(text)

11. Use TRIM to clean the text in cell B4.

(Hint)

Save Question to Custom Practice Set?

LOWER, UPPER, and PROPER Function

Earlier you learned that case (upper vs. lower) usually matters when working with text. In order to manipulate case so that your functions will work properly or to make your data look better use the LOWER, UPPER, and PROPER Functions.

Function	Description
Lower	Changes text to all lower case
Upper	Changes text to all upper case
Proper	Capitalizes the first letter of each word

The only input for these functions is the text string itself.

=LOWER(text)

12. Change the text to all lower case.

(Hint)

Save Question to Custom Practice Set?

13. Capitalize the first letter of each word.

(Hint)