☰

查找字符串中的第一个非数字字符但不排除小数分母

tag-icon • tag-icon microsoft-excel worksheet-function string
查找字符串中的第一个非数字字符但不排除小数分母

基于这个已解决的问题： 查找字符串中的第一个非数字字符

在这个问题中，原帖者请求帮助一个 excel 公式，该公式将返回字符串中第一个非数字字符的位置。这本身就非常有用，并且得到了一个非常干净的解决方案：

{=MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)}

问题是我正在寻找该解决方案的变体，但这不会排除小数或千分母.和,。

我已经寻找一个优雅的解决方案好几个小时了，但我还没有完全找到，所以任何帮助都将不胜感激！

答案1

我有四种方法：

  1.   使用代码或者字符功能
  2.   使用代替功能
  3.   使用递归 LAMBDA 函数
  4. 附加和添加数字

1. CODE/CHAR 方法

TRUE 找到第一个不在查找数组，而 FALSE 则查找第一个查找字符。

示例 1 ( CODE / CHAR )

A1返回第一个字符的位置不是匹配一个字符{","; "."; [0-9]}

# CHAR to CODE version

=XMATCH(TRUE,
   ISERROR(VLOOKUP(
     CODE(MID(A1,SEQUENCE(1,LEN(A1)),1)),
     VSTACK(44,46,SEQUENCE(10,,48)),1,0)))

# CODE to CHAR version

=XMATCH(TRUE,
   ISERROR(VLOOKUP(
     MID(A1,SEQUENCE(1,LEN(A1)),1),
     CHAR(VSTACK(44,46,SEQUENCE(10,,48))),1,0)))

“非”数字、逗号或句点 (CHAR 到 CODE)

“非”数字、逗号或句点（CODE 到 CHAR）

示例 2 ( CODE / CHAR )

A1返回与 中的字符匹配的第一个字符的位置{[A-Z,a-z]}

# CHAR to CODE version

=XMATCH(FALSE,
   ISERROR(VLOOKUP(
     CODE(MID(A1,SEQUENCE(1,LEN(A1)),1)),
     VSTACK(SEQUENCE(26,,65),SEQUENCE(26,,97)),1,0)))

# Since the lookup in this instance is not case-sensitive,
# the formula can be simplified further with UPPER (or LOWER)

=XMATCH(FALSE,
   ISERROR(VLOOKUP(
     CODE(UPPER(MID(A1,SEQUENCE(1,LEN(A1)),1))),
     SEQUENCE(26,,65),1,0)))

# CODE to CHAR version

=XMATCH(FALSE,
   ISERROR(VLOOKUP(
     UPPER(MID(A1,SEQUENCE(1,LEN(A1)),1)),
     CHAR(SEQUENCE(26,,65)),1,0)))

“IS” 是字母表中的一个字母 (CHAR 到 CODE)

“IS” 是字母表中的一个字母 (CODE 到 CHAR)

字符代码示例

CODE("A")=65   /   CHAR(65)="A"

字符 代码
， 四十四
。 四十六
0-9 48-57    =SEQUENCE(10,,48)
亚利桑那州 65-90    =SEQUENCE(26,,65)
亚利桑那州 97-122  =SEQUENCE(26,,97)

2. 替代方法

使用原始配方并添加多个代替将句号和逗号更改为数字的函数，例如0。

  • 挥发性间接公式中的函数被替换为顺序功能：
    SEQUENCE(1,LEN(A1))   代替   ROW(INDIRECT("1:"&LEN(A1)))
=MATCH(TRUE,ISERROR(VALUE(
   SUBSTITUTE(SUBSTITUTE(
     MID(A1,SEQUENCE(1,LEN(A1)),1),
     ".",0),",",0))),0)
A              乙                                                                 C                                                                                                  

特点
----------		 =SUBSTITUTE（
      SUBSTITUTE（A#），“。”，0），“”，0）
-------------------------------------------
=值（B#）
----------------
. 0 0
7 7 7
, 0 0
a a #VALUE!

3. 递归 LAMBDA 方法

我编写了一个名为 LAMBDA 的函数，它调用自身（递归）。公式很简单；它测试一个字符串，看是否可以强制转换为数字但不能强制转换为日期，如果可以，则返回字符串，如果不可以，则从字符串中删除最后一个字符并重复该过程，直到字符串为空或遇到数字。

这种方法适用于更多类型的数字，例如负数和科学计数法，我过滤日期和时间，因为这会引入一些额外的问题。它还会根据您计算机的区域设置将字符串解释为数字。

假设

该公式依赖于以下两个假设：

  1. 中的字符串A1由一个数字和紧随其后的零个或多个“附加字符”组成
  2. “附加字符”永远不会包含数字作为其第一个或第二个字符。
创建命名公式

我在 Excel 的“名称管理器”中命名了公式_getNum。您可以根据需要自定义名称，但请记住，由于公式会自行调用，因此公式中的名称需要与保存公式时的名称相匹配。

# formula returns the number portion of the string

=LAMBDA(str, IF(OR(str="", AND(
    ISERROR(DATEVALUE(str)),
    NOT(ISERROR(VALUE(str))))),str,
    _getNum(LEFT(str,LEN(str)-1))))

或者，此_getPos版本将返回数字字符串后下一个字符的位置

=LAMBDA(str, IF(str="", "",
  IF(AND(ISERROR(DATEVALUE(str)),
     NOT(ISERROR(VALUE(str))))),LEN(str)+1,
    _getPos(LEFT(str,LEN(str)-1))))

可以根据您的需要进行修改，也可以简单地在单元格公式中进行调整。

例如：

  1. 返回数字字符串后第一个字符的位置：
    • _getPos(A1)
    • =LEN(_getNum(A1))+1
  2. 以字符串形式返回数字
    • =_getNum(A1)或者
    • =LEFT(A1,_getPos(A1)-1)
  3. 返回数字后面的字符串
    • =SUBSTITUTE(A1,_getNum(A1),"")
    • =RIGHT(A1,LEN(A1)+1-_getPos(A1))
    • 或自定义命名公式

4. 添加和添加数字方法

此方法利用了逗号和句点是有效数字分隔符（北美）这一事实。在使用 VALUE 函数进行测试之前，1会将其添加到所有字符的前面并附加到所有字符。请注意000OP 评论此公式的早期版本将字母识别E为有效数字，因为1E000是科学计数法，1^0其等于1。我预计它还有其他无法预见的行为，我最初删除了这种方法以支持替代方案，但后来又恢复了它，因为这是 OP 的首选方法。

=LET(str,SUBSTITUTE(A16,"E","F"),
  MATCH(TRUE,ISERROR(VALUE(
     "1"&MID(str,SEQUENCE(1,LEN(str)),1)&"000")),0))
A              乙                                   C                                                                                                  
特点
----------		 ="1"&A#&"000"
------------------------------------		 =值（B#）
---------------
. "1.000" 1
7 "17000" 17000
, "1,000" 1000
a "1a000" #VALUE!

 
 

公式中的换行符

我在较长的公式中添加了换行符。这样我就可以更轻松地阅读和调试它们。当宽度受限时，它们在代码块中显示效果也更好。

除少数例外，大多数函数都允许不必要空格和换行符不会对视觉产生任何影响。显然，字符串内的空格和换行符将成为字符串的一部分

编辑公式时，在公式中插入换行符，使用Alt+Enter

  1. 您可以将包含换行符的公式粘贴到 Excel 的“公式栏”中。
  2. 如果要将其直接粘贴到单元格中，请确保它处于编辑模式，可以通过F2或双击单元格来访问。

将包含换行符的公式粘贴到“公式栏”

相关内容