WPS Table Tutorial: Three seconds to get the first letter corresponding to the word

Click to download: To get the words before the first, two or three Chinese characters Sequencer Template: Get the template of Chinese character sequencer. et


Preview the Template:











in the daily work process, we often need to get the first letter of Chinese characters, for example, students use Sequencer search Word dictionary, the hospital set up a drug inquiry system, KTV set up song query system ... Is there a way to easily get the first letter of the word? As we all know , ET and excle can arrange Chinese data in sequencer, This indicates that the spreadsheet has been able to discern the relationship between the first letter of Chinese pinyin and its position in the encoding system. As long as query the position of Chinese characters in the coding system, and tell the same sequencer of Chinese characters in the encoding of the starting position, the spreadsheet can be sequencer easy to get Chinese characters.


want three seconds to get the first letter of Chinese characters to open your wps form , try it with me!





First, open the original workbook, and paste the following nested formula in the column that prepares the fill letter:





=lookup (CODE (A2), 45217+{ 0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{ "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"})





in this formula, the code function is designed to query the numeric code for the first character of the word (string), which actually indicates the specific rank of the Chinese character in the code base, lookp The function obtains the first letter of the Chinese character according to the starting position of the different sequencer Chinese characters in the code base.


among them, A2 is the name of the cell that refers to the Chinese word, we can get the first letter corresponding to the previous two characters according to the actual situation.





two, if you want to get the words corresponding to the second Chinese character, you can <font face="宋体 ">change</font> the A2 in the above formula to MID (a2,2,1) , which represents the first and second from the second start of the string. The specific formula is:





=lookup (CODE (MID a2,2,1), 45217+{ 0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{ "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"})








If you want to take the third one, and so on, namely:





=lookup (CODE (MID a2,3,1), 45217+{ 0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{ "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"})





three, if want to obtain the first two Chinese characters sequencer how to do? Use " & " to merge the formula to get the first to second Kanji Sequencer. If you want to get the first three characters sequencer use " & " to merge the formulas that get 第一、二、三个 Kanji sequencer.





gets the formula for the first three characters sequencer of the string:


=lookup (CODE (A2), 45217+{ 0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{ "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"}) &amp;lookup (CODE ( MID (a2,2,1)), 45217+{ 0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{ "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"}) &amp;lookup (CODE ( MID (a2,3,1)), 45217+{ 0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{ "A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "W", "X", "Y", "Z"})