Excel sums the text as the corresponding number 100

Use the following method in the string.

, and then add and sum the numbers in ,

1. The number in the string is after a specific Chinese character. Example formula: =-lookup(0,-mid(a1,find(.)"A specific Chinese character",a1)+len("A specific Chinese character"),row(1:

99))) The result of the formula is the number after extracting a specific Chinese character in cell A1.

2. The numbers in the string are at both ends of the entire string. Example formula: number first, formula = left(a1,2*len(a1)-lenb(a1)); The number is at the end, and the formula = right(a1,2*len(a1)-lenb(a1)).

3. The number is in the middle of the string. Formula: Press the key combination Ctrl+Shift+Enter to end the formula.

d2=sumif(a2:a14,a2,b2:b13), sumif a2:a14 is the cell of the condition you ask for and satisfy.

A2 is your condition.

b2:b13 is the cell you ask for and in.

When excel sums, there are Chinese characters after the numbers, and you can use the sumproduct and substitute functions to achieve the sumproduct and substitute functions to achieve the summing with units.

The steps of Fang Nian Hu are as follows:

1. Open the Excel** that needs to be operated, enter the formula =sumproduct(1*substitute() in the cell, and then select the cell where the data needs to be summed into a1:a7.

2. Continue typing"hours","hours"local high dryness;

Enter to complete the formula =sumproduct(1*substitute(a1:a7,"hours","hours".

Among them, A1:A7 is the demand and data, and "Hour Tongdong" is the data unit] <>

3. Return to excel**, and find that there are numbers with Chinese character units behind them to complete the summing operation.

1. Open Excel** on the computer, and add 12 yuan and 13 yuan.

2. Enter the formula =left(j1,2)+left(j2,2), because the number is in front of the text, so use the left formula to extract the number, 2 is to represent two digits.

3. After entering the formula, press the enter key to get the sum of 12 and 13.

4. If the number is after the text, use the right formula, =right(n2,2)+right(n3,3), the principle is the same as the left formula.

5. After entering the formula, you can extract the numbers and add them.

1. Open the data you want to ask for, and take a column of data in the figure to illustrate.

2. Enter = in cell A1"Summing ("&sum(a2:a27)&"

3. The valid value written in this way is from a2 to a27 cells, but such static data is often not what we need, what we need is dynamic, modifiable**, very simple, rewrite the above function as ="Summing ("&sum(a2:a998)&"998 should be enough, if it's not enough, you can add it down.

4. Enter, it must be the same result, we won't take a screenshot here, we can compare the above results by pulling down a few cells.

1.To do this kind of operation on cells, you must ensure that the cells are in numeric format;

2.The format of numbers with text is text format;

3.Therefore, numbers with words cannot be summed;

4.If the data is required, it is recommended to clean the data first and then perform the calculation after the computable requirements are met.

Steps: Here are three options to choose from:

1. Select the first data cell in the sum row, hold down the shift key to select the result cell, and then press the (Auto Sum button) on the toolbar, and click the "OK" button in the pop-up window.

2. Select the cells that require sum, and directly enter the formula "=sum(a1:e1)" in quotation marks.

Description: 1 Replace the line number with the sum; a. Replace the first column number of the sum; e Replace with the last column of the sum.

3. Select the result cell, click the fx (function) button in the column, select the sum function in the pop-up window, then click the first data cell in the summation row, press the left mouse button to pull back to the last data cell in the summation row, release the mouse, and click the "OK" button in the pop-up window.

Don't bother, in fact, you can start a separate column of Chinese characters. If there are no special requirements, it is much more convenient than entering a formula.

Select the cell, right-click "Format Cell", select Custom under the number, and write "?" on the type condition"Ten thousand">> After you make sure that this is set, if you enter any value, it will automatically carry a "10,000" word, if you want any data to be followed by the word "10,000", use "@.""Ten thousand"In this way, the value can be calculated under any circumstances, and the number does not need to be filled in, remember that the quotation marks must be entered in English.