打开“= IF（ISNUMBER（SEARCH”）”回答为数字而不是文本（使用自动求和）(Turn

我试图让Excel中摸出细胞是否含有某快递公司（DHL，DPD等），我希望它发出英里到最近的落客点的数量。 它的工作原理，但它给了我一个文本的答案，而不是一个号码，这意味着自动求和将无法正常工作。 如何更改代码给一个数字的答案吗？ 干杯布赖恩

    =IF(
        ISNUMBER(SEARCH("collect",Q2,1))=TRUE,"1", 
        IF(ISNUMBER(SEARCH("dhl",Q2,1))=TRUE,"2.6", 
        IF(ISNUMBER(SEARCH("DPD",Q2,1))=TRUE,"1", 
        IF(ISNUMBER(SEARCH("inpost",Q2,1))=TRUE,"1.2", 
        IF(ISNUMBER(SEARCH("hermes",Q2,1))=TRUE,"0.6",  
        IF(ISNUMBER(SEARCH("royal",Q2,1))=TRUE,"0.5", 
        IF(ISNUMBER(SEARCH("force",Q2,1))=TRUE,"0.5", 
        IF(ISNUMBER(SEARCH("ups",Q2,1))=TRUE,"0.6",  
        IF(ISNUMBER(SEARCH("yodel",Q2,1))=TRUE,"0", 
        IF(ISNUMBER(SEARCH("collection",Q2,1))=TRUE,"0",
    "No"))

Answer 1:

=IF
  (ISNUMBER(SEARCH("collection",Q2,0))=TRUE,1, 
  IF(ISNUMBER(SEARCH("dhl",Q2,1))=TRUE,2.6, 
  IF(ISNUMBER(SEARCH("DPD",Q2,1))=TRUE,1, 
  IF(ISNUMBER(SEARCH("inpost",Q2,1))=TRUE,1.2, 
  IF(ISNUMBER(SEARCH("hermes",Q2,1))=TRUE,0.6,  
  IF(ISNUMBER(SEARCH("royal",Q2,1))=TRUE,0.5, 
  IF(ISNUMBER(SEARCH("force",Q2,1))=TRUE,0.5, 
  IF(ISNUMBER(SEARCH("ups",Q2,1))=TRUE,0.6,  
  IF(ISNUMBER(SEARCH("yodel",Q2,1))=TRUE,0, 
0))))))))

Answer 2:

如果我正确地按照你的问题，这应该工作。 它的优点是你可以轻松地扩展列表中添加其他的运输和更新的距离。

如果你愿意，你可以使用在B3单元格，而不是VLOOKUP匹配（）指数（）：

=IFERROR(INDEX($E$5:$E$13,MATCH(A3,$D$5:$D$13,0)),"not in list")

我用数据验证在A3单元格的列表中进行选择。

Answer 3:

我只是复制按照意见公式，并删除=TRUE ：

=IF
  (ISNUMBER(SEARCH("collection",Q2,1)),1, 
  IF(ISNUMBER(SEARCH("dhl",Q2,1)),2.6, 
  IF(ISNUMBER(SEARCH("DPD",Q2,1)),1, 
  IF(ISNUMBER(SEARCH("inpost",Q2,1)),1.2, 
  IF(ISNUMBER(SEARCH("hermes",Q2,1)),0.6,  
  IF(ISNUMBER(SEARCH("royal",Q2,1)),0.5, 
  IF(ISNUMBER(SEARCH("force",Q2,1)),0.5, 
  IF(ISNUMBER(SEARCH("ups",Q2,1)),0.6,  
  IF(ISNUMBER(SEARCH("yodel",Q2,1)),0, 
0))))))))

文章来源: Turn '=IF(ISNUMBER(SEARCH')' answer into numeric instead of text (to use autosum)