Shulou(Shulou.com)11/24 Report--

Original title: "Leaders and colleagues are stunned, I actually use Excel's classification like this!"

Excel in the "separation" function, far more powerful than we think, many friends do not know, so today, Xiao Wang teacher to share with you.

1. Split data We often encounter cases where data is piled up in one cell. The content and data are separated by separators. In this case, how should we split it into multiple cells?

Select the column of data, go to Data-Data Tools-Breakdown-Next, and then enter your separator in the box next to Other. Next, select Target Area to complete.

2. How to extract the "date of birth" from the ID card number? Functions! In fact, in addition to functions, we can also do it by dividing!

Select the data column, and then enter the "column"-"fixed width"-"next", in the "data preview", we can click and add a separator in the data below, the "date of birth" separated by a vertical line,"next", in addition to the "date of birth" on both sides of the data select "do not import this column", select "date of birth" as "date", and finally select "target area" to complete.

3. The standard date format is too chaotic, with 2019.5.15; 2018 10 20, and 2020/05/09. How to standardize all of them?

Select the column, go to Data-Data Tools-Breakdown-Next-Next, select Date, click Finish.

Sometimes, we can see that there is a small green triangle next to the cell, because the cell data is in text format, and the text format cannot calculate the formula. By separating functions, we can convert them to each other.

Text-to-number format

Number-to-text format

5. Split the content by province and city. The addresses of provinces and cities are together. We can also split them by dividing them.

Select the province, enter "Province" in "Separate"-"Next"-"Other", enter "Province","Next", then select "Target Area" to complete.

This article comes from Weixin Official Accounts: Word Alliance (ID: Wordlm123), author: Wang Qi

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.