Practical Excel skills sharing: quickly convert text data in batches!

Are you still using column splitting to batch convert text data? No wonder you always work overtime! The following article will share with you two methods of quickly converting text data in batches. I hope it will be helpful to you!

Recently, a friend cried and complained that she often needs to export tabular data from the system, and the exported data types are all text, which is not convenient for subsequent statistics, and the data type needs to be converted. Each conversion takes a lot of time. Take the following table as an example.

She said that she usually uses the data partitioning method to convert column by column, and the operation is super cumbersome.

This is indeed too inefficient. If there are many data columns, use the mouse to continuously click "Collate", "Next" and "Finish" Buttons are so easy to press that your hands will cramp. So what's the solution?

1. Batch conversion using the error checking option button

Perhaps everyone has noticed that the upper left corner of the cell where the text number is located will Displays a green triangle.

The green triangle is the error checking symbol. If the cell is selected at this time, the "Error Checking Options" button will appear on the left side of the cell. Click the drop-down button and the options menu will be displayed. As long as we select the "Convert to Number" option, the cell can be converted from text format to numeric format, as shown in the figure below.

There is a problem, this is only a single cell data type conversion! What we want is batch conversion of multiple cell data types. How to achieve this? It’s not difficult, please be patient and read on.

Operation steps:

Use the mouse to select the cell range that needs to be converted to numerical format, or directly enter the data range in the name box, for example, product For the area from table B2 to M16, enter B2:M16 and press Enter. The "Error Checking Options" button will still appear on the left side of cell B2. Click the button to select the "Convert to Number" option, and the text data type will be converted to a numerical type in batches.

Please see the demonstration effect↓↓↓

## Isn’t it much faster than the column method?

In addition, let me share with you another method, the conversion speed is also very fast.

2. Paste Special Batch Conversion

Operation steps:

Select any blank Cell, press Ctrl C key combination to copy. Then select the data area that needs to be processed, right-click the mouse, select the "Paste Special" command in the pop-up shortcut menu, and in the "Paste Special" dialog box that opens, select "Value" and "Add" buttons, and finally click the "OK" button to realize batch conversion of text data to numerical data. Please see the demonstration effect↓↓↓

##We can also do this:

Select The other steps of the Paste Special operation remain unchanged. In the "Paste Special" dialog box, select "Value" and "Minus" buttons, and finally click the "OK" button;

or enter the number "1" in any blank cell, copy the cell, select the data area that needs to be processed, and click the mouse Right-click and select the "Paste Special" command in the pop-up shortcut menu. In the "Paste Special" dialog box, select The "Value", "Multiply" or "Divide" buttons can also achieve the target task.

Main principle: Excel text numbers can be used as numerical values ​​to directly participate in the four arithmetic operations, and the operation results are numerical values. If you want to keep the numerical value unchanged, you can perform four arithmetic operations with the number "0" or "1". For example, addition, =B2 0; subtraction, =B2-0; multiplication, =B2*1; division, =B2/1.

Let’s give it a try and practice. If we encounter such a problem in the future, we will be able to solve it quickly.

Related learning recommendations:

excel tutorial

The above is the detailed content of Practical Excel skills sharing: quickly convert text data in batches!. For more information, please follow other related articles on the PHP Chinese website!