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

Original title: "this super easy to use Excel text replacement function to help you easily work until the end of the day!" "

Hello, Hi~, I am Tian Tian, who loves to learn from Excel.

It's time for [0 basic learning Excel function] to start the class on time every week.

Today we learn about a very useful text function-the Substitute function.

1. The main function of this function is to replace the characters in the text string.

To make good use of it, you only need to remember three tips:

Where is the text to be replaced?

Which character should be replaced?

Replace it with what?

Let's take a look at the syntax of the Substitute function:

= SUBSTITUTE (text to be replaced, old text, new text, [number to replace])

2. The batch replacement symbol case is a product information table, and all the colons in the table need to be replaced with minus signs. Here, you can use the Substitute function to modify in batches.

❶ enter the = Substitute function in the [B2] cell with parentheses (.

❷ recalls 3 sentences of formula and completes the corresponding operation according to the formula.

Where is the text to be replaced-check the [A2] cell on the left and put a comma

Which character to replace-first enter two English double quotes "" to indicate that this is a text, type a Chinese colon: in the middle of the double quotation marks, and then put a comma to interval.

Replace it with something-- we want to replace it with a minus sign, or first enter two English double quotes "" to indicate the text, and enter a minus mark between the double quotation marks. Fill in the anti-parentheses) and press enter.

Finally, fill the formula down, and all the colons will be replaced with minus signs.

The complete formula is as follows:

= SUBSTITUTE (A2, ":", "-") 3. Replace optional parameters. What should I do if I only want to replace the colon after the English word?

In fact, the Substitute function also has an optional argument that is specifically used to specify the replacement location. When there are multiple identical characters in the same cell, to replace the specified parameter, you need to enable the fourth parameter.

❶ double-click the [B2] cell to enter the formula, enter edit mode, and activate the fourth parameter by placing a comma after the third parameter of the formula.

❷ because we want to replace the second Chinese colon in the cell, so the fourth parameter we directly enter the number [2], after modification, press enter, double-click the lower right corner of the formula cell batch refresh formula, so that the first colon is all retained.

The complete formula is as follows:

= SUBSTITUTE (A2, ":", "-", 2) this article comes from the official account of Wechat: Akiba Excel (ID:excel100), by Tian Tian

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.