Office hacks: SUBSTITUTE function of a few good use

In the Excel table, if the value with the unit, directly with SUM function for statistics, then there will be errors, so the usual practice is to remove the unit and then calculate. In fact, if you use SUBSTITUTE combined with the SUMPRODUCT function, this problem can be easily solved. In addition, SUBSTITUTE function there are other aspects of the wonderful use.

1. circumvent the problem of errors in statistics with units

Take Figure 1 as an example, to calculate the total results with unit figures, use the formula “=SUMPRODUCT(SUBSTITUTE(B2:B9, “million”,””)*1)&”million””, it will circumvent the problem of errors with unit statistics, and can successfully complete the data statistics (Figure 1).


formula to explain: first with SUBSTITUTE function to B2: B9 cells in the region of the “million” replaced by empty, multiplied by 1, the extracted text-based figures into digital figures, and then use the SUMPRODUCT function for and, finally, the sum of the values with “&” linker plus units.

Of course, this formula can also be replaced with “= SUM (–SUBSTITUTE (B2: B9, “million”, “”)) & “million””, press the “Ctrl + Shift + Enter” key combination to make the function effective (Figure (Figure 2).



The role of the two minus signs in the formula is also the text-based numbers into digital numbers.

2. Hidden numbers

In the release of information, in order to protect privacy, sometimes need to hide a few numbers in the phone or ID card, when the use of SUBSTITUTE function is very convenient. For example, use the “=SUBSTITUTE(B2,MID(B2,12,5), “*****”)” formula as shown in the figure, you can achieve the purpose of hiding the ID number figures (Figure 3).


Explanation of the formula: MID function from the identity card number of the 12th bit to extract 5, and then use the SUBSTITUTE function to replace the 5 digits in the identity card number to “*****”.

3. List size statistics

In the registration of participants, there may be more than one name stored in a cell, the statistics of the number of people contained in each cell, the same can also be used SUBSTITUTE function. As shown in the figure, simply use the formula “= LEN (B2) – LEN (SUBSTITUTE (B2, “, “, “”)) +1″ to complete the statistics (Figure 4).


Formula explanation: the first use of the LEN function to calculate the original value of the length of the cell, and then LEN function calculated by the SUBSTITUTE function in the cell, “,” replaced by a space to get the length of the text, the length of the two subtracted and then added 1, the results are the number of people contained in the cell.

Leave a Comment