As shown in figure A1 appeared x1 x2 x3 string, B2 calculate 6

CodePudding user response:

Set the B1 to=Val (Mid (A1, 3, 1)) + Val (Mid (A1, 7, 1)) + Val (Mid (A1, 11, 1))
Then hold down the bottom right hand corner of the B1 s drag down to B3 the lower right corner,

CodePudding user response:

reference 1st floor zhao4zhong1 response:

Settings B1 for=Val (Mid (A1, 3, 1)) + Val (Mid (A1, 7, 1)) + Val (Mid (A1, 11, 1))
Then hold down the bottom right hand corner of the B1 s drag down to B3 at the bottom right,

Thank you for reply! But did not appear as a result, the WPS shall not apply to?
Mainly statistical x column A cell at the back of the string, the sum of the string in front of the number of uncertainty

CodePudding user response:

Correction:
=INT (MID (A1, 3, 1)) + INT (MID (A1, 7, 1)) + INT (MID (A1, 11, 1))

CodePudding user response:

=INT (MID (A1, FIND (" x ", A1, 1) + 1, 1)) + INT (MID (A1, FIND (" x ", A1, FIND (" x ", A1, 1) + 1) + 1, 1)) + INT (MID (A1, FIND (" x ", A1, FIND (" x ", A1, FIND (" x ", A1, 1) + 1) + 1) + 1, 1))

CodePudding user response:

The original poster hello, if only a string of number, please try my code
Input=SUM (IFERROR in B1 (- MID (A1, ROW (an INDIRECT (" 1: "& amp; LEN (A1))), 1), 0)), and then use Ctrl + Shift + Enter to form an array formula, can meet the requirements of the building Lord,

But if some is a digital, some more than a number is the case, the above formula is not ok, let me think about it, this kind of problem, need not VBA is also can do it

CodePudding user response:

Finally found a general formula, assuming that the data in the A1, then input the following formula in the B1
=SUMPRODUCT (TEXT (the LEFT (the TEXT (MID (A1 & amp; "A", the ROW (an INDIRECT (" 1: "& amp; LEN (A1))), COLUMN ($A: $J))), COLUMN ($A: $J) - 1), "0;; 0; ISERR \ 0 ") * (- MID (A1, ROW (an INDIRECT (" 1: "& amp; LEN (A1))) - 1, 2)))
Which use the INDIRECT this formula for dynamic access to the length of the string, and then the COLUMN ($A: $J) this formula, used for dynamically generated 1, 2,... The number of digits of the number sequence, if the string particularly large, more than 10, you need to adjust back J