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

 

[DanteAmor]

Try:

Dante Amor
	A	B
1	Accounting System
2	TRK9815789	TRACK9815789
3	TRK9815789A	TRACK9815789
4	TRK9815789B	TRACK9815789
5	TRK9815789C	TRACK9815789
6	TRK9815789AA	TRACK9815789
7	TRK9815789AAA	TRACK9815789
8
9	Bank
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
Hoja4

Cell Formulas
Range	Formula
B2:B7	B2	=LEFT(SUBSTITUTE(A2,"TRK","TRACK"),12)
B10:B11	B10	=MID(A10,SEARCH("TRACK",A10),12)

 

[bebo021999]

This search for 1 digit to 10-digit number after "TRK" or "TRACK"
I am using Ex2016. (For 365, it should be simpler )

Book1
	A	B
1	Accounting System
2	TRK9815789	TRACK9815789
3	TRK9815789A	TRACK9815789
4	TRK9815789B	TRACK9815789
5	TRK9815789C	TRACK9815789
6	TRK9815789AA	TRACK9815789
7	TRK9815789AAA	TRACK9815789
8		NOT FOUND
9	Bank	NOT FOUND
10	Account:1000, Bank:Bank of England, TRACK9815789 CUSTOMER: Accounts Payable	TRACK9815789
11	Account:1000, Bank:Bank of Scotland, Type:Checking TRACK9820789aa Party:Henry Lee	TRACK9820789
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR("TRACK"&AGGREGATE(14,6,LEFT(MID(SUBSTITUTE(A2&" ","TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2&" ","TRK","TRACK"))+5,SEARCH(" ",SUBSTITUTE(A2&" ","TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2&" ","TRK","TRACK"))+5)-(SEARCH("TRACK",SUBSTITUTE(A2&" ","TRK","TRACK"))+5)),{1,2,3,4,5,6,7,8,9,10})+0,1),"NOT FOUND")

 

[DanteAmor]

Maybe:

Dante Amor
	A	B
1	Accounting System
2	TRK9815789	TRACK9815789
3	TRK9815789A	TRACK9815789
4	TRK9815789B	TRACK9815789
5	TRK9815789C	TRACK9815789
6	TRK9815789AA	TRACK9815789
7	TRK9815789AAA	TRACK9815789
8		NOT FOUND
9	Bank	NOT FOUND
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
Hoja4

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(MID(SUBSTITUTE(A2,"TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2,"TRK","TRACK")),12),"NOT FOUND")

 

[pto160]

Thanks.

Excel is not great at seeing Text using text functions. The TRACK id could vary in length. I am trying to get the TRACK ID to end in a number. I probably could use an OR statement, that if the last character ends in text, then use the substitute function to make the last character an empty string, the second last character an empty string and so on.
The max number of letters at the end would be 3.
The bank part is there is always a space before the track then the word and then a space. Since it is a bank statement, the TRACK part could be different parts of the string. This is sorta tricky. I could use a separate formula for that if that is easier.

Book1
	A	B	C	D
1	Accounting System	What I am getting	What I want	Formula
2	TRK9815789789	TRACK8157878	TRACK9815789	TRACK9815789
3	TRK9815789474A	TRCK9815789474	TRACK9815789474	TRACK9815789
4	TRK9815789B	TRACK9815789	TRACK9815789	TRACK9815789
5	TRK9815789C	TRAK9815789	TRACK9815789	TRACK9815789
6	TRK9815789AA	TRCK9815789	TRACK9815789	TRACK9815789
7	TRK981578945AAA	TRACK981578945AAA	TRACK981578945	TRACK9815789
8
9	Bank		What I want
10	Account:1000, Bank:Bank of England, TRACK981578923 CUSTOMER: Accounts Payable		TRACK981578923	TRACK9815789
11	Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207891 Party:Henry Lee		TRACK98207891	TRACK9820789
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(SUBSTITUTE(A2,"TRK","TRACK"),RIGHT(A2,1),"")
B7	B7	=SUBSTITUTE(A7,"TRK","TRACK")
D10:D11,D2:D7	D2	=IFERROR(MID(SUBSTITUTE(A2,"TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2,"TRK","TRACK")),12),"NOT FOUND")

 

[bebo021999]

Thanks.

Excel is not great at seeing Text using text functions. The TRACK id could vary in length. I am trying to get the TRACK ID to end in a number. I probably could use an OR statement, that if the last character ends in text, then use the substitute function to make the last character an empty string, the second last character an empty string and so on.
The max number of letters at the end would be 3.
The bank part is there is always a space before the track then the word and then a space. Since it is a bank statement, the TRACK part could be different parts of the string. This is sorta tricky. I could use a separate formula for that if that is easier.

Book1
	A	B	C	D
1	Accounting System	What I am getting	What I want	Formula
2	TRK9815789789	TRACK8157878	TRACK9815789	TRACK9815789
3	TRK9815789474A	TRCK9815789474	TRACK9815789474	TRACK9815789
4	TRK9815789B	TRACK9815789	TRACK9815789	TRACK9815789
5	TRK9815789C	TRAK9815789	TRACK9815789	TRACK9815789
6	TRK9815789AA	TRCK9815789	TRACK9815789	TRACK9815789
7	TRK981578945AAA	TRACK981578945AAA	TRACK981578945	TRACK9815789
8
9	Bank		What I want
10	Account:1000, Bank:Bank of England, TRACK981578923 CUSTOMER: Accounts Payable		TRACK981578923	TRACK9815789
11	Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207891 Party:Henry Lee		TRACK98207891	TRACK9820789
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(SUBSTITUTE(A2,"TRK","TRACK"),RIGHT(A2,1),"")
B7	B7	=SUBSTITUTE(A7,"TRK","TRACK")
D10:D11,D2:D7	D2	=IFERROR(MID(SUBSTITUTE(A2,"TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2,"TRK","TRACK")),12),"NOT FOUND")

Have you tried my formula in #3? Its for vary lenght of digit number upto 10 digits

 

[Peter_SSs]

What about these?

23 02 02.xlsm
	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
9	Bank	What I want
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
TRK

Cell Formulas
Range	Formula
B2:B7	B2	=LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(CONCAT(IF(ISNUMBER((0&MID(A2,SEQUENCE(LEN(A2)),1))+0),REPT(" ",50),1)),50))))
B10:B11	B10	=LET(p,FIND("TRACK",A10),REPLACE(LEFT(A10,FIND(" ",A10,p)-1),1,p-1,""))

 

[JV0710]

Info in Cell A2: TRK9815789333AAABBB
Formula in cell B2: ="Track"&CONCAT(IFERROR(0+MID(E5,SEQUENCE(LEN(E5)),1),""))
Result in B2: Track9815789333

 

[Georgiboy]

My effort - one formula for both:

Book1
	A	B
1	Accounting System	Georgiboy
2	TRK9815789789	TRACK9815789789
3	TRK9815789474A	TRACK9815789474
4	TRK9815789B	TRACK9815789
5	TRK9815789C	TRACK9815789
6	TRK9815789AA	TRACK9815789
7	TRK98157899945AAA	TRACK98157899945
8
9	Bank
10	Account:1000, Bank:Bank of England, TRACK98123 CUSTOMER: Accounts Payable	TRACK98123
11	Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207998891 Party:Henry Lee	TRACK98207998891
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=IF(ISNUMBER(FIND("TRK",A2)),"TRACK"&TEXTJOIN("",TRUE,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")),IF(ISNUMBER(FIND("TRACK",A2)),"TRACK" & TEXTBEFORE(TEXTAFTER(A2,"TRACK")," "),""))

 

[JV0710]

formula in B10: ="Track"&CONCAT(IFERROR(0+MID(MID(E7,FIND("TRACK",E7),LEN(E7)),SEQUENCE(LEN(MID(E7,FIND("TRACK",E7),LEN(E7)))),1),""))