Extract Text at End of String

[pto160]

I am on the latest version of Excel 365.
I decided to set up a new thread because this is a different question even though there is some similarities to the thread below that I posted.

Extract Text using one formula

I have the latest version of Excel 365 with all the new functions. :) so I am wondering if extracting text will be easier. So if cell in column A says "Bank" then the result returned will be the text in column B. There is two scenarios with the cell named GL in column A where the text string...

www.mrexcel.com

I am comparing my transaction ID in my accounting system to the bank. I am trying to pull the tracking ID so I can compare them and use the filter formula.
In the accounting system there could be letters at the end, but I want to remove them in a different column. I am using the substitute formula, but I am getting the incorrect result.

On the bank statement, there is always a space at the end of the tracking number and a space before. I wrapped the text in this example.
TRACK9815789 CUSTOMER: Accounts Payable

Book1
	A	B	C
1	Accounting System	What I am getting	What I want
2	TRK9815789	TRACK81578	TRACK9815789
3	TRK9815789A	TRCK9815789	TRACK9815789
4	TRK9815789B	TRACK9815789	TRACK9815789
5	TRK9815789C	TRAK9815789	TRACK9815789
6	TRK9815789AA	TRCK9815789	TRACK9815789
7	TRK9815789AAA	TRACK9815789AAA	TRACK9815789
8
9	Bank		What I want
10	Account:1000, Bank:Bank of England, TRACK9815789 CUSTOMER: Accounts Payable		TRACK9815789
11	Account:1000, Bank:Bank of Scotland, Type:Checking TRACK9820789 Party:Henry Lee		TRACK9820789
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(SUBSTITUTE(A2,"TRK","TRACK"),RIGHT(A2,1),"")
B7	B7	=SUBSTITUTE(A7,"TRK","TRACK")

 

[Peter_SSs]

Looking at post #8, I realise that I mis-read the requirement for the top section.
But for now I will stick with my suggestion for the second section,

 

[JvdV]

A lot of nice answers around allready!

---

The below will look extensive but I'll post it anyways since it will check for any substring that:

* Starts with either 'TRK' or 'TRACK';
* When starts with 'TRK', check that the string from position 4 onwards is numeric for the total length of the same substring when all uppercase alpha chars are replaced. This would ensure that there could only be trailing characters left en the number part is a continuation of digits;
* The same as the previous would happen when the substring starts with 'TRACK', but only from position 6 onwards;
* In case the union of the two patterns has more than 1 hit, choose the 1st one.

Book1
	A	B
1	Accounting System	What I want
2	TRK9815789789	TRK9815789789
3	TRK9815789474A	TRK9815789474
4	TRK9815789B	TRK9815789
5	TRK9815789C	TRK9815789
6	TRK9815789AA	TRK9815789
7	TRK981578945AAA	TRK981578945
8	TRK1B2
9	Bank
10	Account:1000, Bank:Bank of England, TRACK981578923 CUSTOMER: Accounts Payable	TRACK981578923
11	Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207891 Party:Henry Lee	TRACK98207891
12	Account:1000, Bank:Bank of Scotland, TRK98207891ABC Party:Henry Lee	TRK98207891
13	Account:1000, Bank:Bank of Scotland, TRK98207891ABC123 or TRACK12345ABC Party:Henry Lee	TRACK12345
14	Account:1000, Bank:Bank of Scotland, TRK123 or TRACK123 Party:Henry Lee	TRK123
Sheet1

Cell Formulas
Range	Formula
B2:B14	B2	=MAP(A2:A14,LAMBDA(z,LET(x,FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,TEXTSPLIT(z,{",";" "}))&"</s></t>","(//s[starts-with(.,'TRK')][substring(.,4, string-length(translate(., 'ABCDEFGHIJKLMNOPQRSTUVWXYA', '')))*0=0]|//s[starts-with(.,'TRACK')][substring(.,6, string-length(translate(., 'ABCDEFGHIJKLMNOPQRSTUVWXYA', '')))*0=0])[1]"),IFERROR(TEXTBEFORE(x&"D",TEXTSPLIT(x&"D",{"TRK";"TRACK";0;1;2;3;4;5;6;7;8;9},,1),-1),""))))
Dynamic array formulas.

 

[Rick Rothstein]

Give this formula a try in cell B2...

=MAP(A2:A14,LAMBDA(x,LET(t,"TRACK"&IFNA(TEXTBEFORE(TEXTAFTER(SUBSTITUTE(UPPER(x),"TRK","TRACK"),"TRACK")&"X",CHAR(SEQUENCE(26,,65))),""),IF(LEN(t)>6,t,""))))

 

[shinigamilight]

You can try this if you are allowed to use macros, cuz regex gives you much more control over what you're searching.

Sub REG()
        Dim Regex As Object
        Set Regex = CreateObject("vbscript.regexp")
        Dim mc As Object
        With Regex
            .Global = True
            .Pattern = "(TRACK|TRK)[0-9]+"
        
        End With
        On Error Resume Next
        
        For Each rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
                Set mc = Regex.Execute(rng.Value)
                rng.Offset(0, 1) = mc(0)
        Next rng

End Sub

 

[Peter_SSs]

You can try this if you are allowed to use macros, cuz regex gives you much more control over what you're searching.

Not important, but I don't see any benefit in going to the trouble of setting .Global = True for that code.

 

[shinigamilight]

This was the original sample data i tested on to get both the matches, though I didn't spill the second match in the corresponding cell as it wasn't asked by op.

 

[JvdV]

This was the original sample data i tested on to get both the matches, though I didn't spill the second match in the corresponding cell as it wasn't asked by op.

Just a headsup, but that was not original sample data from OP. I added these edge-cases in my answer in post #12 just to showcase how my formula would work a little different to the other answers.

FYI, I explicitly wanted to avoid matching the TRK98207891ABC123 example as **no** match, hence the sample and use of xpath-expressions to assert this is true.

 

[pto160]

Thanks so much to everyone. My head is spinning. These formulas are great. This will make my work so much easier.

Rick- I did not think to add a condition that if it did not find TRK or TRACK leave the cell blank. Thanks for adding this. I have never heard of MAP. It seems powerful when combined with LAMDA.

PeterSS_s- I wrapped the formula in substitute, which seems to work. The sequence function is a great function. Thank you.

=substitute(LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(CONCAT(IF(ISNUMBER((0&MID(A2,SEQUENCE(LEN(A2)),1))+0),REPT(" ",50),1)),50)))),"TRK","TRACK"

 

[Peter_SSs]

Glad we could help. Quite a few choices!