Excel公式要求从特定字符中获取最后一个数字

Question

我只想得到最后一个下划线(_)后面的数字。如果不用逗号作为分隔符拆分字符串，如何做到这一点？

• PSN_IDA8_776 =需要得到776
• NXXT_FAEMNE_7905 =需要得到7905
• PCBA_SAN_LUIS_441B =需要得到441B
• MCOM_LUX_415_U =需要得到415 U的

下面的内容适用于PSN_IDA8_776

=RIGHT(I9,FIND("_",I9)-1) 

但是相同的公式不能正确地适用于ABCA_SEA_3_SFA_809

Answer 1

假设您的数据放在A1:A6中

在复制的B1公式中：

=SUBSTITUTE(MID(A1,LOOKUP(9^9,FIND("_"&{1,2,3,4,5,6,7,8,9,0},A1))+1,99),"_","")

编辑：

正如Terry指出的那样，上述公式将在字符串中失败，如：TEST_30_20_10_AB

以下是测试数据A1:A7，与Terry的文章相同

PSN_IDA8_776       
NXXT_FAEMNE_7905
PCBA_SAN_LUIS_441B
MCOM_LUX_415_U
ABCA_SEA_3_SFA_809
TEST_30_20_10_AB
TEST_aa_20_bb_10

我在B1中修改的公式，抄录下来：

=SUBSTITUTE(MID(A1,AGGREGATE(14,6,FIND("_"&{1,2,3,4,5,6,7,8,9,0},A1),1)+1,99),"_","")

公式的结果：

B1: 776       
B2: 7905
B3: 441B
B4: 415U
B5: 809
B6: 10AB
B7: 10

Answer 2

试一试

=MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))+1,LEN(A1))

分解成单独的公式：

C1：=LEN(A1) --查找原始字符串的长度。

D1：=LEN(SUBSTITUTE(A1,"_",""))查找去掉下划线的字符串的长度。

E1：=C1-D1将返回字符串中的下划线数。

F1：=SUBSTITUTE(A1,"_","|",E1) -用I-bar代替最后一个下划线.

G1：=MID(A1,FIND("|",F1,E1)+1,LEN(A1)) -在最后一个下划线之后取出字符。

或者对于VBA解决方案：

Public Function After_Underscore(Target As Range, Optional Delimiter As String = "_") As Variant

    Dim Last As Long

    Last = InStrRev(Target, Delimiter)
    If Last > 0 Then
        After_Underscore = Mid(Target, Last + 1, Len(Target))
    Else
        After_Underscore = CVErr(xlErrNA) 'If no underscore return an #N/A error.
    End If

End Function

Answer 3

下面的解决方案需要使用TEXTJOIN，它仅在Excel365和更高版本中可用。

=IF(RIGHT(TEXTJOIN("",0,--ISNUMBER(FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","t/s"))),2)="10",SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",100),SUMPRODUCT(--(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1)="_"))-1),100)),"_",""),MID(A1,MAX(IFERROR(FIND("_",A1,ROW($A$1:INDEX($A:$A,LEN(A1)))),0))+1,LEN(A1)))

替换A1以适应您的情况。它是一个Ctrl+Shift+Enter数组公式，所以在输入公式栏后，必须在键盘上按下它。

主要的逻辑是找出最后两个子字符串(后面跟着下划线_)是否是numeric + text的组合。如果是，则返回两个子字符串的组合，否则只返回最后一个子字符串。

下面是我测试过的示例字符串：

| Strings            | Results |
|--------------------|---------|
| PSN_IDA8_776       | 776     |
| NXXT_FAEMNE_7905   | 7905    |
| PCBA_SAN_LUIS_441B | 441B    |
| MCOM_LUX_415_U     | 415U    |
| ABCA_SEA_3_SFA_809 | 809     |
| TEST_30_20_10_AB   | 10AB    |
| TEST_aa_20_bb_10   | 10      |