Columns in Excel table

Columns in Excel table

Text-Numeric format conversion

The data that is exported from the system often has a number that looks like a numeric value, actually a text format. For example, in the following figure, select the check-in time for column D and show only the count in the status bar, not the sum.

Such data can not be directly completed by the summary analysis, the use of the function of the column, the text will be quickly converted to numeric format.

Click the D column label, click data → break down, and click OK in the Pop-up Text Column Wizard dialog box.

After a simple process, the text-type numbers in this column all turn into numeric numbers.

Second, clear invisible characters

Using the same method, you can also clear out the invisible characters in your data. If the data that is exported from the system looks normal and the format is regular, but cannot be summed up normally, try the method of disaggregation to deal with it.

Iii. convert six-bit date

In the following illustration, column A is a 6-digit number, representing four-bit years, two-bit months, and two-digit days, respectively.

This kind of data is certainly not unfamiliar to us, but in Excel, such data cannot be recognized as date format. If you want to do a phased summary by date, you need to do the necessary processing of the data.

Click "Next" → "Next" in the Pop-up Text Column Wizard, by clicking data → columns.

Column data Format Select Date, select YMD in the Drop-down box, and click Finish.

The data that is processed in this way becomes the real date.

Four, clear the space in the ID number

As shown in the following illustration, the personnel information is derived from the system. Before the ID number, you can see an empty cell, how to remove the cell in bulk?

If you use the replacement method directly, even if column C is formatted as text, the back three digits of the ID number will become 0, and once you save and close Excel, the data will not be recovered.