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

The original title: "after reading the name of China Aerospace, I unexpectedly found four Excel skills." "

Hi, I'm Xiaolan who knows a little bit about Excel.

Two days ago, there was a hot search # only China Aerospace can come up with such an excellent name #, using a video to show you the "Heavenly Palace" space station, "Heaven and" core module, "Mengtian" and "Wen Tian" experimental module. The "Chinese romance" behind these names.

For example, the "Heavenly Palace" as we know it comes from the famous poet Su Shi's phrase "I don't know the heavenly palace, what year it is tonight". The Heavenly Palace is also the place where the Emperor of Heaven lived in ancient mythology, naming the space station Tiangong. It places our desire to build a beautiful home in space.

As big as the space station, as small as flowers and plants, in fact, names can be seen everywhere in life, that is, the Excel that cousins often come into contact with can give you a "name" column.

Needless to say, let's take a look at what is in this column.

The surnames of most of us are one Chinese character, but a small number of people are compound surnames with two characters, such as Ouyang and Shangguan.

If they are all single surnames, you can use the LEFT function to extract the last name at a time, and the RIGHT and LEN functions to extract the first name.

For example, the following example.

❶ enters the formula in cell B2:

= LEFT (A2Pol 1)

❷ enters the formula in cell C2:

= RIGHT (A2, (LEN (A2)-1))

If they are all compound surnames, change the formula in unit B2 to:

= LEFT (A2Pol 2) but what if there is both a last name and a double surname?

The solution is here:

❶ create a new worksheet, fill in the common duplicate surnames in this table, and create a [compound surname database] (I have built it, and there is a way to get it at the end of the article).

▲ partial compound surname ❷ enter the formula in cell B2:

= IFERROR (VLOOKUP (LEFT (A2Magne2), database of compound surnames! Avor Amen1), LEFT (A2Magne1))

The formula roughly means: first use the LEFT function to extract the first two words of the A2 cell name, and then use the VLOOKUP function to find a match in the "complex surname database". If the match is successful, it will return the result of LEFT (A2Power2) (compound surname); if the match fails, it will return the result (surname) of LEFT (A2Co1).

❸ enters the formula in cell C2:

= IF (LEN (B2) = 1Magi right (A2, (LEN (A2)-1)), RIGHT (A2, (LEN (A2)-2)

The formula roughly means: first use the LEN function to determine whether the number of characters in B2 cell is equal to 1, yes, return the result of RIGHT (A2, (LEN (A2)-1); no, return the result of RIGHT (A2, (LEN (A2)-2).

Name alignment sometimes, when we hand over the following form to the leader, he will feel that the surname list is not "neat" enough.

In this case, we can come up with the method of "decentralized alignment"!

❶ select column B data, press the shortcut key [CTRL+1] to open the [format cells] dialog box.

❷ in the alignment tab, set horizontal alignment to spread alignment, indent to 1, and OK.

Got it!

Name coding in some cases, we need to code the name, such as the following figure:

You can use the REPLACE function.

But. If there is a compound surname and the first name has two words, this method will have some defects.

Do you have any good solutions?

At the end of the review, we have learned a total of three "name" related skills today:

❶ names are separated

❷ name alignment

❸ name coding

The methods are very simple, and the most important thing is to practice more in order to master it more quickly.

By the way, if you still want to learn "how to generate random names", I recommend using the plug-in [square grid] and click on it.

Just such a small move may save you time scratching your head back and forth.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhu Lan, Editor: Zhu Lan

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.