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

Original title: "Code numbers, one function is enough!"

Hello everyone! I'm Chang Xiaoan ~

Today, the company collected employee information and got a data form about ID card number, but because employee information needs to pay attention to privacy security, I am required to type code at a specific location. How to operate?

For example: the above is the ID number provided by the employee, which is required to start from the seventh digit and type * for the last four digits to protect privacy.

The final effect is as follows:

I believe that most of the white people who don't know Excel may honestly change them manually one by one and live overtime without pay.

This was obviously time-consuming and laborious!

Before solving the problem, we need to analyze it first.

For example, the data in cell B2 is changed from 8888 to ****,

Cell B3 is to replace 7777 with ****, and so on, starting from the seventh digit, and replacing the next four digits with ****.

With this in mind, you can actually use functions related to substitution to process such data.

Here, Xiao An Amway gives you a very useful function- Replace.

Replace, replace, Replace

How does it work? Take your time!

Let's first understand the operation principle of the function to help everyone better understand the [Replace] function ~

The principle of the function is to replace an old string with a new string at the specified position and number.

As the saying goes: a picture is worth a thousand words.

The animation demonstrates as follows:

See here, do you understand the operation principle of this function!

First let's look at the official formula for the Replace function:

=REPLACE(old_text,start_num,num_chars，new_text)

I don't know what you mean.

Don't panic, Xiao An will translate it for you ~

=REPLACE (original data, where to start, how many bits to replace, what to replace)

Parameter 1: Select the cell to be replaced as the original data;

Parameter 2: Where to start replacing;

Parameter 3: Replace several digits;

Parameter 4: What to replace.

Still don't understand? It doesn't matter. We'll take this case as reference.

Case demonstration purpose: replace the 4 digits of ID number data in column B from the 7th digit with ****.

Enter the Replace function;

(Tip: Enter = REP to press Tab to quickly select formulas.)

Parameter 1 selects cell B2 as the original data to be replaced; parameter 2 input 7: represents replacement from the seventh bit;

Input 4 for parameter 3: replace the four-digit number starting from the seventh digit; input "****" for parameter 4: the character string to be replaced;

Double-click Fill Down.

Formula:

=REPLACE(B2,7,4,"****") animation demonstration as follows:

Code the numbers in the right places, and that's it!

This is the end of the introduction to the Replace function. Have you learned it?

This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), Author: Smart Qiu Xiao E

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.