Practical Excel skills sharing: 3 ways to quickly replace part of numbers with asterisks!

In order to protect customer privacy, we usually change customer phone numbers to * numbers. How is this achieved? Here are 3 ways to hide local phone numbers with asterisks, take a look!

Each salesperson often comes into contact with a large number of customers and will use a small notebook to write down the information of many customers, and the mobile phone number is one of the important items. In order to protect customer privacy, salespeople need to change part of the customer's mobile phone number into asterisks in public information.

For example, if you change customer A’s mobile phone number 15867852976 to 158****2976, how can you use EXCEL to meet the requirements?

Here I introduce three practical tips to cleverly hide your mobile phone number.

1. Splicing method

The basic idea is to extract the mobile phone number in segments, and then splice it into a new field with the substitution.

1. Use the CONCATENATE function to splice

You can use the LEFT and RIGHT functions to extract the first three and last four digits of the mobile phone number as the first and last numbers of the CONCATENATE function. Three parameters, while using the symbol "****" as the second parameter of the CONCATENATE function. Just concatenate the above three parameters through the CONCATENATE function. The formula is as follows:

=CONCATENATE(LEFT(B2,3),"****",RIGHT(B2,4))

2. Directly use connectors & splicing

The above formula can be written as:

= LEFT(B2,3)&"*** *"&RIGHT(B2,4)

Of course, you can also use the MID function to extract multiple digits of the mobile phone number and then splice them together.

2. Function replacement method

You can directly use the REPLACE function of EXCEL to replace the specified characters. The formula is as follows:

=REPLACE(B2,4,4,"****")

##Formula explanation:

• B2: Specify the text that needs to be replaced. Here, cell B2 is specified.

• #4: Specify the starting position of replacement, 4 means starting from the 4th character.

• #4: Specify the number of characters to be replaced, 4 means 4 characters need to be replaced.

• ****: The replaced text, here means that the replaced text is 4 *. (Double quotes must be added at both ends of text data)

This function is relatively simple and easy to understand.

3. Quick filling method

When our EXCEL version is 2013 or above, our work efficiency will be higher. In this version of the filling function, there is a function called "quick filling", which is a function similar to "imitation".

We first enter "158****2976" in cell C2. In order to enable the computer to recognize text patterns more accurately, it is recommended that cell C3 also enter numbers according to the style. Then select cells C2 and C3, place the mouse in the lower right corner of the cell area, when the cursor takes on a black cross shape, drag the mouse downward; in the "Autofill Options", click "Quick Fill", and the computer will imitate us According to the input rules, the required mobile phone number will be automatically generated.

Is this method more efficient?

Related learning recommendations:

excel tutorial

The above is the detailed content of Practical Excel skills sharing: 3 ways to quickly replace part of numbers with asterisks!. For more information, please follow other related articles on the PHP Chinese website!