4 tips for using the substitute function in Excel function learning

In the previous article "Practical Excel Skills Sharing: Using Pivot Tables and VLOOKUP Functions to Analyze Accounts Receivable", we learned about using Excel pivot tables and VLOOKUP functions to analyze accounts receivable method of analysis. The following article will talk about how to use the substitute function. Come and collect it and learn it!

First let’s take a look at the usage of the substitute function. The substitute function is somewhat similar to the find and replace command in excel, but it is more flexible and easy to use.

Thesubstitute function is a text search function, which searches for a certain character and then replaces it with another character.

We use vernacular to simply understand the usage of the substitute function: Substitute (text, old text, new text, replace from which number) .

Case 1: Basic usage skills of the Substitute function

How to use the function substitute, we use a simple case to understand. Upgrade the model. If the upgrade rule starts with model A, change all models to start with B. The effect is shown in column D:

The formula in cell D2 is: =SUBSTITUTE(C2,"A","B")

That is, use the Substitute function to replace "A" with "B".

Case 2: Substitute function in excel hides the middle four digits of the mobile phone number

##C2 The cell formula is:

=SUBSTITUTE(B2,MID(B2,4,4),"****")

Use MID to extract the middle four digits of the mobile phone number, and then use Substitute The function replaces it with four

*.

Case 3: substitute function substitution case: counting the number of people on duty

C2 cell The formula is:

=LEN(B2)-LEN(SUBSTITUTE(B2,",","")) 1

First use the LEN function to count the Substitute function to replace the commas between the persons on duty The length of the string after deletion. By using the string length in cell B2 - removing the comma, the string length is 1, you can count the number of people on duty.

Case 4: Use Substitute function to calculate sales total

D11 cell formula For:

=SUMPRODUCT(SUBSTITUTE(D2:D10,"元","")*1)&"元"

##SUBSTITUTE(D2:D10,"元", "")

means to replace all "yuan" in column D with null values, and finally multiply by 1 to convert the text into a numerical value. Reuse the SUMPRODUCT function to calculate the sum. Finally, use the text connector to add "yuan" and add the unit.

#Through the above four cases, I believe that friends have learned how to use the function substitute.

Related learning recommendations:

excel tutorial

The above is the detailed content of 4 tips for using the substitute function in Excel function learning. For more information, please follow other related articles on the PHP Chinese website!