CODEWITHSUNDEEP
excel-formula
Guna
Guna

Reputation: 11

Excel formula require to get last number from specific character

I want to get just the number after the last underscore (_). How do I do that without splitting the string with the comma as a delimiter?

The following is working for PSN_IDA8_776

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

but same formula not correctly working for ABCA_SEA_3_SFA_809

Upvotes: 1

Views: 100

Answers (3)

bosco_yip
bosco_yip

Reputation: 3802

Assume your data put in A1:A6

In B1 formula copied down :

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

Edit:

As per Terry W's point out that the above formula will fail in string like : TEST_30_20_10_AB

Here is the testing data A1:A7, as same as Terry W's post mentioned

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

My revised formula in B1, copied down :

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

Result of the formula :

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

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

Try

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

Broken down into separate formula:
C1: =LEN(A1) - find the length of the original string.
D1: =LEN(SUBSTITUTE(A1,"_","")) find the length of the string with the underscore removed.
E1: =C1-D1 will return the number of underscores in the string.
F1: =SUBSTITUTE(A1,"_","|",E1) - substitute the last underscore with an I-bar.
G1: =MID(A1,FIND("|",F1,E1)+1,LEN(A1)) - pull out the characters after the last underscore.

or for a VBA solution: 

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

Upvotes: 1

Terry W
Terry W

Reputation: 3257

The following solution requires the use of TEXTJOIN which is only available in Excel 365 and later versions.

=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)))

replace A1 to suit your case. It is an array formula so you MUST press Ctrl+Shift+Enter on your keyboard after entering it in the formula bar.

The main logic is to find out if the last two sub-string (followed by underscore _) is a combination of numeric + text. If so, return the combination of both sub-string, otherwise just return the last sub-string.

Here are the sample strings I have tested:

| 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      |

Upvotes: 0

Related Questions