Four classic cases to take you to play Excel must function of the Substitute function

The word Substitute means to replace. The substitute function is somewhat similar to the find-replace command in excel, but is much more flexible and useful.

The substitute function belongs to the text finder class, which is to find a character and replace it with another character.

Simple to understand grammatical structure: Substitute (text, old text, new text, replace from the first).

Case 1: Substitute function basic usage skills

Upgrade the models with the upgrade rule starting with A. Change all models to start with B. The effect is shown in column D.

**D2The cell formula is：=SUBSTITUTE(C2,"A","B")**

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

Case 2: Hiding the middle four digits of a mobile phone number

**C2The cell formula is：=SUBSTITUTE(B2,MID(B2,4,4),"****")**

Use the MID to extract the middle four digits of the cell phone number and then use the Substitute function to replace them with four *s.

Case 3: Counting the number of people on duty

**C2The cell formula is：=LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1**

First, use the LEN function to count the length of the string after the comma is removed between the duty officers using the Substitute function. The number of people on duty can be counted by using the length of the string in cell B2 - the length of the string with the comma removed + 1.

Case 4: Total sales

**D11The cell formula is：=SUMPRODUCT(SUBSTITUTE(D2:D10," primary","")*1)&" primary"**

SUBSTITUTE(D2:D10," primary","") The meaning of this is to putD column in the“ ten thousand” Replace all with null values， multiplying the last by1， Converting text to values。

Then use the SUMPRODUCT function to find the sum. Finally, add "$" with a text linker and add the units again.

Graphic from: Tribal Nest Education

Graphic layout: 16 Accounting 301 Fan Xiaoqi

Director of Operations: 16 Accounting 303 Tan Hongxia