See today: On and off in Excel data tables

The first sentence of "The Two Kingdoms" is "the trend of the world, divided for a long time will be combined, and long must be divided", this sentence with a dialectical thought of the word revealed the world of all things in the changing law. As a person in office, often need to deal with some of the data on and off, if not a few tricks, some people fear a round of surrender.

Data splitting and extraction

Student Xiao Liu is a sales manager with excellent performance, when he records customers ' mailing addresses and zip codes, they put them in a cell (Figure 1), and by the end of the year, when you want to use the mail merge feature in Word to send a greeting card message to each customer, look at three thousand or four thousand customer information, Only to find such a record of the problem, and quickly send an e-mail to me for help. Luckily, there is a space in the middle of the address and zip code data in this Excel document, so you can easily handle it within 10 seconds.

Figure 1 Datasheet

(1) Data splitting operation

Because of the large amount of data, you first select the entire column by the column number. Go to the data → columns command, select the default separator symbol option, and click the Next button. Then select the middle "space" option and see a preview of the columns below. Then go to "next" (see Figure 2) and click the "Finish" button, all right.

Figure 2 Data splitting

If there is no space in the middle, the previous customer address information text length is inconsistent? It is not difficult, 15 seconds to complete. You cannot use the columns command to use Excel's text extraction function. In this extract function in Excel, there are 3 kinds: "Left", "right" and "mid", which can extract the text characters from the cell from "Zuo", "R" and "Middle" respectively. In this example, "left" and "right" are used respectively to extract customer address information and postal codes.

(2) The operation of the text extraction function

First take a look at the right "ZIP code" data extraction. Set the cursor in the B2 cell, input formula "=right (a2,6)", after entering, can automatically extract the data source A2 cell to the right of the 6th text character, that is, the postal code data information, the last fill handle can calculate all the right side of the address of the 6-bit postal code information (see Figure 3).

Figure 3 Text Extraction