How to replace string in Excel table

Question: How to replace strings in Excel tables efficiently? Problem introduction: When processing large amounts of text data, replacing strings is a common requirement, and manual operations are time-consuming and laborious. PHP editor Xinyi has compiled a simple and easy-to-understand method for replacing strings in Excel tables to help you easily solve this problem. Read the following details to master the skills of replacing strings in Excel tables and improve data processing efficiency.

1. If you want to generate the required format, you can use the replacement function (REPLACE), the string length function (LEN), and the generate repeated text function (REPT).

2. Description of the function formula that needs to be used.

The function to be replaced, the function formula is: REPLACE (the string text to be replaced, starting from which character, the number in the string is replaced, the new string).

(2) Function to calculate the length of a string, the function formula is: LEN (referencing the string in the cell)

(3) Repeatedly displayed text function, the function formula is: REPT( The string to be generated, the number to be generated)

3. Open the Excel table and enter the formula =REPLACE($A2,2,LEN($A2)-2,REPT("* in cell B2 ",LEN($A2)-2)), then drag down to copy the fill, and then you will see the desired style.

4. Function formula =REPLACE($A2,2,LEN($A2)-2,REPT("*",LEN($A2)-2)) explanation.

(1) The string to be replaced is the string in the referenced cell A2.

(2)Start from the 2nd position.

(3) The replacement length is [LEN($A2)-2], that is: get the length of the string from cell A2 and subtract one from the beginning and the end, and the rest is what needs to be replaced.

(4) The new string is [REPT("*",LEN($A2)-2)], that is: use [LEN($A2)-2] and [*] replacements need to be replaced String.

5. If you want to retain the two mantissa characters, you can change [LEN($A2)-2] in the formula to [LEN($A2)-3].

6. Each function is an independent individual. If you combine them cleverly, you can handle many things. Of course, you should also understand them before using them in combination, so that you can make the best use of them.

The above is the detailed content of How to replace string in Excel table. For more information, please follow other related articles on the PHP Chinese website!