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

I'm Laxiaden, an Excel teacher who can design forms.

What I want to share with you today is how to display the ID card number in segments.

If a person does not want to learn Excel, the best way is to arrange an exam for him.

An exam can force people to grovel, or they can make people get carried away, get an inch and get it.

Did you see the smile on my face?

1. The problem of method one is very simple to solve.

The requirement is to add a space according to the length of 68 4.

Then split the text according to its length and merge it together.

❶ LEFT function to extract the first 6 characters. Extract 6-bit characters from the left and use the LEFT function to do it.

The formula is as follows:

= LEFT (A2pj6) is very simple, isn't it?!

❷ MID function to extract the middle 8-bit characters. The middle 8 bits, starting with the 7th character, need to extract 8 characters, so use MID as a matter of course.

The formula is as follows:

It's not difficult to MID, isn't it?

The ❸ RIGHT function, which extracts 4 characters on the right. The last four digits are on the far right, so with the RIHGT function, can you think of anything simpler?

The formula is as follows:

= RIGHT (A2P5) Hey? Isn't it 4 digits extracted? how do you write 5 here?

Because there is a space at the end of the identity number, in order to avoid the emergence of scientific counting!

You see, one sentence exposes your own level. Calm down, calm down!

❹ text linker to quickly merge text. Finally, use the & symbol, plus a space "" to link the previous characters, and the result comes out.

The formula is as follows:

= B2 & "& C2 &" & D2 is clear, and the formula is not difficult to write, is it?

Method 2. If the examiner adds a sentence, you must use a function to solve the problem.

Well... no, no, no.

Of course, it won't stop me!

It is done by a formula, and the formula is as follows:

= TEXTJOIN ("", 1rem mid (A2, {1rem 7je 15}, {6je 8pr 4})) Wow, the formula is so long?

Don't be afraid, in fact, the train of thought is the same as before.

❶ MID function to extract 683% of the text respectively. Earlier, we used the LEFT, RIGHT, and MID functions to extract the text corresponding to the length of 683.

In fact, what we can all extract with the MID function is to set the actual character and the extracted length respectively.

You see, it's just as easy to do with three MID functions.

The key is, how to use a function according to the requirements of the examiner?

Then merge these three into one function.

The formula is as follows:

= MID (A2, {1pt7pr 15}, {6pj8pr 4}) is what it looks like after merging, and the contents in {curly braces} are arrays.

Is equivalent to the cell 1, 7, 15 extraction position, 6, 8, 4 extraction length, put into the formula.

Praise me for being a smart kid.

❷ TEXTJOIN instead of text links match and join text. The function of TEXTJOIN is to link the text together using specified delimiters, and its structure is like this.

So with the results of MID, merging text with TEXTJOIN is a natural thing.

= TEXTJOIN (", 1Dony mid (A2, {1pr 7d15}, {6pc8pr 4})) this article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: bin Laden Dony

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.