Excel includes functions for textual strings to reformat, separate, combine, and rewrite the text. These functions can be combined together to do advanced actions when working with textual strings.
In this chapter, we will reference data in Figure 32.1 that includes a City, State, Zip Code text string, and Population and Rank data. However, we only need the former text string.
Use this workbook for the chapter.
For ease of reference, it is recommended to freeze our top row(s) containing headers and the left column(s) containing the City, State, Zip Code information. We can do this by selecting the cell below the header row and right of the aforementioned column, then going to the View tab of the ribbon toolbar, and selecting Freeze Panes and Freeze Panes again.
The Lower function is used to edit a text string to have all lowercase letters. In this example, we will use it to lowercase all letters in the city, state, and zip code information cell.
The Upper function is used to edit a text string to have all uppercase letters. We can use it to uppercase all letters in the city, state, and zip code information cell.
The Proper function is used to edit a text string to uppercase the first letter of each word. Our original reference data already has words formatted in this manner, but we can use one of the previously completed Lower or Upper function cells for reference.
The Left function can be used to pull and display a designated number of characters beginning from the left of a text string.
Identical to the Left function, the Right function can be used to pull and display a designated number of textual characters beginning from the right of a text string. For this example, we'll pull five characters to copy the zip code at the end of the text string.
The Length function counts the number of characters in a cell's text string.
The Trim function removes any extra spaces found within the cell's text string. The data for city, state, and zip code includes two spaces between state and zip code information. We can use Trim to remove the second space.
An ampersand symbol instructs Excel to combine two references in an argument.
Similar in function to the ampersand, the CONCAT (Concatenate) function combines multiple cell references into one cell.
Note: Like the previous function example, you may need to include a space between quotation marks to separate the selected items. For example, the Figure 32.11 formula would look like the following: =CONCAT(M3," ",K3," ",J3)
The Value function converts numbers used in a textual string format to number format. In the case of the zip code produced in the earlier Right function section, we'll use Value combined with Right to convert the zip code into a numerical format whereby it will be right-aligned in the cell.
The Find function is used to locate the first position of an indicated character in a referenced textual string. Note: This function is case-sensitive.
Identical to the Find function, the Search function will locate the first position of an indicated character in a text string. Note: This function is not case-sensitive.
The Replace function is capable of rewriting a designated portion of the referenced cell's text. In this example, we'll reference the city, state, and zip code cell, indicate a starting point, determine the number of characters from that point to be replaced, and insert a new string.
Substitute works similarly to the Replace function by searching for a designated character or text to be substituted and inserting the new text.
The Middle function works like the Left and Right functions by beginning at a specified position in a text string and pulling a determined number of characters.
Now, we want to extract individual data from the City, State, Zip Code column. We will start by finding the city using the =LEFT function, then the state using the =MID function, and finally, the zip code using the =RIGHT function.
The City column will be our first step. In this column, we’ll be using the =LEFT function. Use the following steps to extrapolate the city from the City, State, Zip Code column:,
The State column is a little trickier. In this column, we’ll be using the =MID function. Use the following steps to extrapolate the state from the City, State, Zip Code column:
If you ever want to make sure you have those extra spaces removed, you can always wrap the entire formula in a trim function, which will remove the extra spaces for you. (See Figure 32.20)
The Zip Code will be the most straightforward step. In this column, we’ll be using the =RIGHT function. Use the following steps to extrapolate the zip code from the City, State, Zip Code column:
Now that you have the City, State, and Zip Code in separate columns, select all three input cells and click and drag down to automatically update the columns. It will even account for spaces between words, like Little Rock.
These text functions are invaluable when manipulating data; take the time to learn them to manipulate text quickly and accurately.