Practical Excel skills sharing: 3 ways to quickly extract cell data

How to quickly extract cell data in Excel? The following article summarizes 3 extraction methods for you, ranging from the simplest shortcut key operations to the imaginative space replacement method. Extracting cell data can only use 99 spaces! I really want to pry open the heads of some great gods to see how they get along with each other in such incredible ways.

It is necessary to extract three sets of data of capacitance, packaging and voltage resistance from the specifications and models, as follows:

The data source is in column A. The amount of data is very large. The byte positions of the three data that need to be extracted, namely capacitance, packaging and voltage resistance, are not fixed in the cell. The rule that can be found is that the extracted data is located after the second, third, and fourth commas of the data source.

When we encounter a problem, finding the pattern is the key to solving the problem. Now that the pattern is found, so is the solution. There are three methods here, ranging from the simplest shortcut key operation to the imaginative space replacement method. They are introduced below.

1. Quick filling method Ctrl E

Operation points:

(1) Enter in cell B2 At 0402, enter a single quote first, or change the cell to text format before entering;

(2) Entering only one data may not get the correct result through Ctrl E. At this time, enter two data continuously. That's it.

Tip: The key combination Ctrl E can only be used in Excel 2013 and above.

As far as this example is concerned, Ctrl E is a little troublesome, so I will introduce another method of using columns.

2. Column separation method

Operation points:

(1) Use commas in the column separation process Separate;

(2) You need to skip the columns that are not imported;

(3) Set the data in the value column to text format;

(4) Manually Specify the target area for data storage.

Compared with the first method, using sorting is much simpler. At the same time, through this example, everyone can also have an in-depth understanding of the powerful function of sorting.

Although it is more convenient to use columns, if you often need to process this kind of data, the amount of operations is quite large. Finally, we will share a formula method.

3. Brainstorm: TRIM-MID-SUBSTITUTE-REPT combination formula

Use the formula: =TRIM(MID(SUBSTITUTE($A2 ,",",REPT(" ",99)),COLUMN(B1)*99,99))

Pull down to the right to get the desired result.

Formula analysis:

Five functions are used in this formula, including the MID and COLUMN that we are more familiar with, and the TRIM, SUBSTITUTE and REPT functions that we are less commonly used. Let’s briefly explain this formula Idea.

The core part of the formula is SUBSTITUTE($A2,",",REPT(" ",99)). The function of this part is to replace.

The format of the SUBSTITUTE function is:

SUBSTITUTE（在哪里替换，替换什么，换成什么，换第几个）

For example:

Formula=SUBSTITUTE($A2,",","-",3)The effect is to replace the third comma in cell A2 Replace with - sign.

When the fourth parameter is omitted, it means that all commas are replaced, as shown in the figure:

In this example, the commas in A2 are replaced by REPT(" ",99), which is 99 spaces.

The format of the REPT function is:

REPT（要重复的字符，重复次数）

For example:

REPT(“★”,5) means repeating ★ five times.

As for why 99 spaces are used in the formula , is completely a routine. You may understand it if you continue to read the other parts of the formula.

The data obtained using SUBSTITUTE also needs to be extracted using the MID function. Everyone should be familiar with the MID function. The basic format is : MID (data source, where to start taking, how many words to take). In this example, the data source to be extracted is SUBSTITUTE(), and the location of the value to be extracted is originally after the second comma, because We replaced the comma with 99 spaces. There are at least two sets of spaces in front of the position to be extracted, which is 2*99 characters; the extracted position of the corresponding package is 3*99, and the pressure-resistant one is 4*99. Use the formula right Pull, so COLUMN(B1)*99 is used as the extraction position here. The last parameter of MID is to extract several characters. For the sake of safety, 99 characters are extracted uniformly.

In other words, after the calculation of the MID (SUBSTITUTE (), COLUMN (B1) * 99, 99) part of the formula, the result is that the capacity data we actually need is included in the preceding and following spaces. In order to facilitate everyone's understanding, temporarily replace the spaces with -, and you can intuitively see the effect:

We definitely don't want the results to contain a large number of useless spaces, so Set a TRIM function in the outermost layer to remove these spaces. The TRIM function has only one parameter, and its function is to remove extra spaces in the string.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: 3 ways to quickly extract cell data. For more information, please follow other related articles on the PHP Chinese website!