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

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

Recommended>>
1、Python in Office 365 development
2、Cloudsights free image recognition API for colleges and universities
3、A little cloud computing science
4、Big news Humans are going to lose their jobs as artificial intelligence steps into all sorts of fields
5、Foxconn sets up AI artificial intelligence company in Silicon Valley focuses on factory floor automation

已推荐到看一看 和朋友分享想法
最多200字，当前共 发送

已发送

确定
分享你的想法...
取消

确定
最多200字，当前共

发送中