如何在 Excel 中的文本字符串中查找包含同一字符串中的其他数字的十进制数

Question 1

要返回字符串中第一个非整数，您可以使用过滤掉非十进制数字的函数FILTERXML：xPath

=FILTERXML("<t><s>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",","),")",","),",","</s><s>")&"</s></t>","//s[floor(number(.)) != number(.) and number(.)=number(.)]")

这将返回整个数字，而不仅仅是小数点后七位。

创建xml：

"<t><s>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",","),")",","),",","</s><s>")&"</s></t>"

xpath 参数

"//s[floor(number(.)) != number(.) and number(.)=number(.)]"

如果您确实希望将值截断到小数点后 7 位，那么您还需要执行一些数学函数。

编辑

如果您的模式始终相同，并且所需值是最后一个逗号分隔的值，后跟一个括号，那么您也可以尝试：

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)),LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)))-1)

这将返回一个文本字符串。

返回数值：

=--LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)),LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)))-1)

并且只有 7 位小数：

=TRUNC(--LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)),LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)))-1),7)

Answer

要返回字符串中第一个非整数，您可以使用过滤掉非十进制数字的函数FILTERXML：xPath

=FILTERXML("<t><s>" &SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",","),")",","),",","</s><s>")&"</s></t>","//s[floor(number(.)) != number(.) and number(.)=number(.)]")