excel有每箱的规格一栏：1*6*800g，如何用公式计算出每箱的总重？即4800g

Question

excel有每箱的规格一栏：1*6*800g，如何用公式计算出每箱的总重？即4800g

Answer 1

=LEFT(A1,FIND("*",A1)-1)*PRODUCT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"g",""),"*",REPT(" ",50)),{1,2}*50,50))*1)

这样取就可以的，记得采纳

Answer 2

如果你的数据都是：3个数和2个乘号及最后一个字符是单位这样的数据结构格式的话，可以用公式实现，如果数据太不规则了，也许只能手工核算了……

追问

哪个公式呢？

追答

假如你的规格数据是在A2单元格的，则可在B2单元格输入公式：
=EVALUATE(LEFT(A2,LEN(A2)-1))
这其中，你操作的EXCEL必须是2016以上的版本，否则这个 EVALUATE 函数是没有的！

Answer 3

我以数据在A1单元格为例=LEFT(A1,FIND("*",A1)-1)*MID(A1,FIND("*",A1)+1,FIND("*",A1,FIND("*",A1)+1)-FIND("*",A1)-1)*SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("*",A1,FIND("*",A1)+1)+1,LEN(A1)-FIND("*",A1,FIND("*",A1)+1)),"g",""),"k",""),"t","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"*","")