MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

You can see the power of text functions by using them to solve some actual problems that were sent to me by former students working for Fortune 500 corporations. Often, the key to solving problems is to combine multiple text functions into a single formula.

• I have a worksheet in which each cell contains a product description, a product ID, and a product price. How can I put all the product descriptions in column A, all the product IDs in column B, and all the prices in column C?

• In this example, the product ID is always defined by the first 12 characters, and the price is always indicated in the last 8 characters (with two spaces following the end of each price). Our solution, contained in the file Lenora.xlsx and shown in Figure 5-2, uses the LEFT, RIGHT, MID, VALUE, TRIM, and LEN functions.

  Figure 5-2: Using the TRIM function to trim away excess spaces.

• It’s always a good idea to begin by trimming excess spaces, which we do by copying from B4 to B5:B12 the formula TRIM(A4). The only excess spaces in column A turn out to be the two spaces inserted after each price. To see this put the cursor in cell A4 and select F2 to edit the cell. If you move to the end of the cell, you will see two blank spaces. The results of using the TRIM function are shown in Figure 5-2. To prove that the TRIM function removed the two extra spaces at the end of cell A4, you can use the formulas =LEN(A4) and =LEN(B4) to show that cell A4 contains 52 characters and cell B4 contains 50 characters.

• To capture the product ID, we need to extract the 12 leftmost characters from column B. We copy from C4 to C5:C12 the formula LEFT(B4,12). This formula extracts the 12 left-most characters from the text in cell B4 and the following cells, yielding the product ID, as you can see Figure 5-3.

  Figure 5-3: Using text functions to extract the product ID, price, and product description from a text string.

• To extract the product price, we note that the price occupies the last six digits of each cell, so we need to extract the rightmost six characters from each cell. I copy from cell D4 to D5:D12 the formula VALUE(RIGHT(B4,6). I use the VALUE function to turn the extracted text into a numerical value. Without converting the text to a numerical value, you couldn’t perform mathematical operations on the prices.

• Extracting the product description is much trickier. By examining the data, we can see that if we begin our extraction with the 13th character and continue until we are 6 characters from the end of the cell, we can get the data we want. Copying from E4 to E5:E12 the formula MID(B4,13,LEN(B4)–6–12) does the job. LEN(B4) returns the total number of characters in the trimmed text. This formula (MID for Middle) begins with the 13th character and then extracts the number of characters equal to the total number less the 12 characters at the beginning (the product ID) and the 6 characters at the end (price). This subtraction leaves only the product description!

• Now suppose we are given the data with the product ID in column C, the price in column D, and the product description in column E. Can we put these values together to recover our original text?

• Text can easily be combined by using the CONCATENATE function. Copying from F4 to F5:F12 the formula CONCATENATE(C4,E4,D4)) recovers our original (trimmed) text, which you can see in Figure 5-3.

• The concatenation formula starts with the product ID in cell C4. Next we add the product description from cell E4. Finally, we add the price from cell D4. We have now recovered the entire text describing each computer! Concatenation can also be performed by using the & sign. We could recover the original product ID, product description, and price in a single cell with the formula C4&E4&D4. Note that cell E4 contains a space before the product description and a space after the product description. If cell E4 did not contain these spaces then the formula C4&" "&E4&" "&D4 would have inserted the necessary spaces. Note that the space between each pair of quotes results in the insertion of a space.

• If the product IDs did not always contain 12 characters, this method of extracting the information would fail. We could, however, extract the product IDs by using the FIND function to discover the location of the first space. Then we could obtain the product ID by using the LEFT function to extract all characters to the left of the first space. The example in the next section will show how this approach works.

• If the price did not always contain precisely six characters, extracting the price would be a little tricky. See Problem 15 for an example of how to extract the last word in a text string.

• Every day I receive data about total U.S. sales, which is computed in a cell as the sum of East, North, and South regional sales. How can I extract East, North, and South sales to separate cells?

• This problem was sent to me by an employee in the Microsoft finance department. She received a worksheet each day containing formulas such as =50+200+400, =5+124+1025, and so on. She needed to extract each number into a cell in its own column. For example, she wanted to extract the first number (East sales) in each cell to column C, the second number (North sales) to column D, and the third number (South sales) to column E. What makes this problem challenging is that we don’t know the exact location of the character at which the second and third numbers start in each cell. In cell A3, the North sales begin with the fourth character. In cell A4, the North sales begin with the third character. The data we’re using in this example is in the file Salesstripping.xlsx, shown in Figure 5-4. We can identify the locations of the different regions’ sales as follows:

  • East sales are represented by every character to the left of the first plus sign (+).

  • North sales are represented by every character between the first and second plus signs.

  • South sales are represented by every character to right of the second plus sign.

  Figure 5-4: Extracting East, North, and South sales with a combination of the FIND, LEFT, LEN, and MID functions.

  By combining the FIND, LEFT, LEN, and MID functions, we can easily solve this problem as follows:

  • Use the Edit, Replace command to replace each equal sign (=) with a space. To remove the equal signs, select the range A3:A6. Then on the Home tab in the Editing group, click Find & Select, and then click Replace. In the Find What field, enter an equal sign (=) and leave Replace With blank. Then click Replace All. This converts each formula into text by replacing the = sign by a space.

  • Use the FIND function to locate the two plus signs in each cell.

  We begin by finding the location of the first plus sign for each piece of data. By copying from B3 to B4:B6 the formula FIND("+",A3,1), we can locate the first plus sign for each data point. To find the second plus sign, we begin one character after the first plus sign, copying from C3 to C4:C6 the formula FIND("+",A3,B3+1).

  To find East sales, we use the LEFT function to extract all the characters to the left of the first plus sign, copying from D3 to D4:D6 the formula LEFT(A3,B3-1). To extract the North sales, we use the MID function to extract all the characters between the two plus signs. We begin one character after the first plus sign and extract the number of characters equal to (Position of 2nd plus sign)–(Position of 1st plus sign) – 1. If you leave out the –1, you’ll get the second + sign. (Go ahead and check this.) So, to get the North sales, we copy from E3 to E4:E6 the formula MID(A3,B3+1,C3–B3–1).

  To extract South sales, we use the RIGHT function to extract all the characters to the right of the second plus sign. South sales will have the number of characters equal to (Total characters in cell) – (Position of 2nd plus sign). We compute the total number of characters in each cell by copying from F3 to F4:F6 the formula LEN(A3). Finally, we obtain South sales by copying from G3 to G4:G6 the formula RIGHT(A3,F3-C3).