Office hacks: use wildcards to extract the information you need

Excel data exported in some systems, there are often a lot of jumbled characters, if these data alone in Excel to extract the information needed (such as Chinese characters, numbers, English), often too much work, not efficient. But if you use Excel in conjunction with Word, plus the aid of wildcards, in these jumbled data to extract the required information, it is very easy (Figure 1).

1820A-XXTQ-1

1. get Chinese

First, select all the data in the Excel worksheet that holds the miscellaneous data columns, copy and paste them into a new blank Word document (Figure 2).

1820A-XXTQ-2

Select the Word document in the data column, click “Replace”, in the pop-up window to find the contents of the input “[! a – a summit]”, “Replace with” at nothing to enter, click “More”, in the expanded panel check the “use wildcard”, click Click “Replace All”. In this way, all non-Chinese characters in the data column will be deleted, leaving only Chinese. Cut and paste the replaced data into the Excel worksheet on it (Figure 3).

1820A-XXTQ-3

Tip: In the above expression, the “[! I – EKEU]” is the key, “[I – EKEU]” means the Chinese character wildcard, which means all Chinese characters from “I” to “EKEU” in the GBK code table (喺 pinyin: dǎo). And “[! I – I am]”, the “!” in “[! is a negative word, which means that all characters except the Chinese character are replaced by spaces. 2.

2. Get the numbers

re-copy and paste the miscellaneous data from the Excel worksheet to a blank Word document, select the Word document in the data column, click “Replace”, in the pop-up window to find the contents of the input “[!0-9]”. “Replace with” where nothing is entered, click “More”, in the exhibit panel check the “Use Wildcard”, click “Replace All “. In this way, all non-numeric characters in the data column will be removed, leaving only numbers. Cut and paste the replaced data into the Excel worksheet on it (Figure 4).

1820A-XXTQ-4

Tip: Meaning of “[!0-9]”: Since the numeric wildcard is “[0-9]”, the same “!” is negative, so “[!0-9]” means non-numeric. 3.

3. Get English

To get the English characters, you need to do two steps. Find and replace in the Word document, the first “[0-9]” replaced by what is also not entered; and then “[a – enlace]” replaced by what is also not entered. In this way, the data column numbers and Chinese characters were all deleted, leaving only English characters. Cut and paste the replaced data into the Excel worksheet will be able to.

Leave a Comment