Little E has prepared this article supporting case files (including code), Excel essential toolbox and square grid installation package for everyone,
Receive a direct attention to the male brown number [Autumn Leaf Excel], reply to [1020]!
Hello everyone, I've been busy lately to flying green water zero.
Some time ago, colleague Xiao Wang asked me for advice: How to extract pinyin initials in Excel?
Because every time you update customer information, you need to enter the customer's full name to retrieve:
This is very inconvenient, so he wants to extract the first letter of the name for search:
This requirement looks quite difficult, but it is not a problem to achieve it, and there are still many methods
Let the Word Brothers take a shot, or use VBA, and all kinds of Excel plugins!
<h1 class="pgc-h-arrow-right" data-track="21" >01 Word+Excel brotherhood method</h1>
❶ Copy the name column and paste it to Word in a "keep text only" fashion.
❷ Use Ctrl+H to open the Find and Replace window, click the "More (M)" button, check "Use wildcards", and fill in the English character question mark "?" in the search content (represents any single character); in the replacement field, fill in "^&" (note that ^is preceded by a space).
"^&" refers to the original search for the content, the effect of adding a space in front of it, that is, adding a space in front of each character.
Effects of the replacement:
The purpose of this substitution is to facilitate the extraction of the initials later, which is not necessary if it is only to be converted to pinyin.
❸ Select the content that needs to be converted to Pinyin, click the [Home] tab - [Pinyin Guide] - [OK].
After adding the effect of pinyin:
❹ Copy and paste the converted pinyin back to Excel, and use the formula in column C to the right of the pinyin column (column B):
=PHONETIC(B2)
The role of the PHONETIC function is to extract the phonetics.
Then fill it down, so that the pinyin is extracted.
❺ Copy the Pinyin area, paste the results of the formula area as values, and then use the Columnization function of the Data tab to split the Pinyin into multiple columns in word units.
❻ Use the Left function to extract the initials of pinyin.
The left function starts from the left and extracts the number of characters that are needed, because we only extract the first letter, so the second argument can be defaulted, and then use & to stitch.
This method does not require any plug-ins or code, but the steps are a bit much.
Next, let's look at PlanB, the VBA custom function method.
<h1 class="pgc-h-arrow-right" data-track="70" >02 VBA custom function method</h1>
❶ Press [Alt+F11] to open the VBA editor, right-click in the left project window, and insert the module.
❷ Paste the code into the window for Module 1 and close the VBA Editor. (See the end of the article for the code acquisition method~)
❸ Use the custom function pyszm() to get the first letter of pinyin.
Isn't that a lot simpler! After all, writing code to lose hair, let the big guys do it, we just need to stand on the shoulders of the big guys and be lazy.
But! There are more convenient ways to oh ~
Our public account has recommended several plugins, which can also be used to extract the initials~
<h1 class="pgc-h-arrow-right" data-track="92" >03 plug-in method</h1>
Here is the demonstration of excel must-have toolbox, interested in this plug-in small partners can take a look at our public medium [Autumn Leaf Excel] past articles ↓↓↓
Still working overtime for Excel? Come and try this freeware and click the mouse to get most of the day working!
❶ Select the name area to be converted;
❷ Click [Toolbox] tab - Other functions - Chinese characters to Pinyin;
❸ Select "Just Pinyin Initials" for the conversion method, uncheck "Add Space", click [Batch Convert Selected Areas], and get it!
<h1 class="pgc-h-arrow-right" data-track="103" >04 summary</h1>
❶ Word has a limited number of functions to extract pinyin, taking Office 365 as an example, you can extract 90 characters (note that the characters contain characters such as spaces and line breaks), so it is not very efficient when extracting pinyin in batches.
The code for ❷ VBA may not be able to cover all characters, but it should be sufficient for everyday use.
❸ The plug-in demonstrated in the case is free, and the square grid can also implement this function, but the square grid is charged for a fee.
Although the method of Word is a little cumbersome, it uses the functions of Word and Excel itself to achieve the method of extracting pinyin, and there is no requirement for the version
Do you know anything else that may be a bit off-the-beaten-path, but fun or useful features? Welcome to share in the message area~