Extract Text at End of String

P

pto160

Active Member
Joined
Feb 1, 2009
Messages
482
Office Version
  1. 365
Platform
  1. Windows
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.
www.mrexcel.com

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 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
ABC
1Accounting SystemWhat I am gettingWhat I want
2TRK9815789TRACK81578TRACK9815789
3TRK9815789ATRCK9815789TRACK9815789
4TRK9815789BTRACK9815789TRACK9815789
5TRK9815789CTRAK9815789TRACK9815789
6TRK9815789AATRCK9815789TRACK9815789
7TRK9815789AAATRACK9815789AAATRACK9815789
8
9BankWhat I want
10Account:1000, Bank:Bank of England, TRACK9815789 CUSTOMER: Accounts PayableTRACK9815789
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK9820789 Party:Henry LeeTRACK9820789
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=SUBSTITUTE(SUBSTITUTE(A2,"TRK","TRACK"),RIGHT(A2,1),"")
B7B7=SUBSTITUTE(A7,"TRK","TRACK")
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Try:

Dante Amor
AB
1Accounting System
2TRK9815789TRACK9815789
3TRK9815789ATRACK9815789
4TRK9815789BTRACK9815789
5TRK9815789CTRACK9815789
6TRK9815789AATRACK9815789
7TRK9815789AAATRACK9815789
8
9Bank
10Account:1000, Bank:Bank of England, TRACK9815789 CUSTOMER: Accounts PayableTRACK9815789
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK9820789 Party:Henry LeeTRACK9820789
Hoja4
Cell Formulas
RangeFormula
B2:B7B2=LEFT(SUBSTITUTE(A2,"TRK","TRACK"),12)
B10:B11B10=MID(A10,SEARCH("TRACK",A10),12)
 
Upvote 0
This search for 1 digit to 10-digit number after "TRK" or "TRACK"
I am using Ex2016. (For 365, it should be simpler )
Book1
AB
1Accounting System
2TRK9815789TRACK9815789
3TRK9815789ATRACK9815789
4TRK9815789BTRACK9815789
5TRK9815789CTRACK9815789
6TRK9815789AATRACK9815789
7TRK9815789AAATRACK9815789
8NOT FOUND
9BankNOT FOUND
10Account:1000, Bank:Bank of England, TRACK9815789 CUSTOMER: Accounts PayableTRACK9815789
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK9820789aa Party:Henry LeeTRACK9820789
Sheet1
Cell Formulas
RangeFormula
B2:B11B2=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")
 
Upvote 0
Maybe:

Dante Amor
AB
1Accounting System
2TRK9815789TRACK9815789
3TRK9815789ATRACK9815789
4TRK9815789BTRACK9815789
5TRK9815789CTRACK9815789
6TRK9815789AATRACK9815789
7TRK9815789AAATRACK9815789
8NOT FOUND
9BankNOT FOUND
10Account:1000, Bank:Bank of England, TRACK9815789 CUSTOMER: Accounts PayableTRACK9815789
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK9820789 Party:Henry LeeTRACK9820789
Hoja4
Cell Formulas
RangeFormula
B2:B11B2=IFERROR(MID(SUBSTITUTE(A2,"TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2,"TRK","TRACK")),12),"NOT FOUND")
 
Upvote 0
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
ABCD
1Accounting SystemWhat I am gettingWhat I wantFormula
2TRK9815789789TRACK8157878TRACK9815789TRACK9815789
3TRK9815789474ATRCK9815789474TRACK9815789474TRACK9815789
4TRK9815789BTRACK9815789TRACK9815789TRACK9815789
5TRK9815789CTRAK9815789TRACK9815789TRACK9815789
6TRK9815789AATRCK9815789TRACK9815789TRACK9815789
7TRK981578945AAATRACK981578945AAATRACK981578945TRACK9815789
8
9BankWhat I want
10Account:1000, Bank:Bank of England, TRACK981578923 CUSTOMER: Accounts PayableTRACK981578923TRACK9815789
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207891 Party:Henry LeeTRACK98207891TRACK9820789
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=SUBSTITUTE(SUBSTITUTE(A2,"TRK","TRACK"),RIGHT(A2,1),"")
B7B7=SUBSTITUTE(A7,"TRK","TRACK")
D10:D11,D2:D7D2=IFERROR(MID(SUBSTITUTE(A2,"TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2,"TRK","TRACK")),12),"NOT FOUND")
 
Upvote 0
pto160 said:
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
ABCD
1Accounting SystemWhat I am gettingWhat I wantFormula
2TRK9815789789TRACK8157878TRACK9815789TRACK9815789
3TRK9815789474ATRCK9815789474TRACK9815789474TRACK9815789
4TRK9815789BTRACK9815789TRACK9815789TRACK9815789
5TRK9815789CTRAK9815789TRACK9815789TRACK9815789
6TRK9815789AATRCK9815789TRACK9815789TRACK9815789
7TRK981578945AAATRACK981578945AAATRACK981578945TRACK9815789
8
9BankWhat I want
10Account:1000, Bank:Bank of England, TRACK981578923 CUSTOMER: Accounts PayableTRACK981578923TRACK9815789
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207891 Party:Henry LeeTRACK98207891TRACK9820789
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=SUBSTITUTE(SUBSTITUTE(A2,"TRK","TRACK"),RIGHT(A2,1),"")
B7B7=SUBSTITUTE(A7,"TRK","TRACK")
D10:D11,D2:D7D2=IFERROR(MID(SUBSTITUTE(A2,"TRK","TRACK"),SEARCH("TRACK",SUBSTITUTE(A2,"TRK","TRACK")),12),"NOT FOUND")
Click to expand...
Have you tried my formula in #3? Its for vary lenght of digit number upto 10 digits
 
Upvote 0
What about these?

23 02 02.xlsm
AB
1Accounting SystemWhat I want
2TRK9815789789TRK9815789789
3TRK9815789474ATRK9815789474
4TRK9815789BTRK9815789
5TRK9815789CTRK9815789
6TRK9815789AATRK9815789
7TRK981578945AAATRK981578945
8
9BankWhat I want
10Account:1000, Bank:Bank of England, TRACK981578923 CUSTOMER: Accounts PayableTRACK981578923
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207891 Party:Henry LeeTRACK98207891
TRK
Cell Formulas
RangeFormula
B2:B7B2=LEFT(A2,LEN(A2)-LEN(TRIM(RIGHT(CONCAT(IF(ISNUMBER((0&MID(A2,SEQUENCE(LEN(A2)),1))+0),REPT(" ",50),1)),50))))
B10:B11B10=LET(p,FIND("TRACK",A10),REPLACE(LEFT(A10,FIND(" ",A10,p)-1),1,p-1,""))
 
Upvote 0
Info in Cell A2: TRK9815789333AAABBB
Formula in cell B2: ="Track"&CONCAT(IFERROR(0+MID(E5,SEQUENCE(LEN(E5)),1),""))
Result in B2: Track9815789333
 
Upvote 0
My effort - one formula for both:
Book1
AB
1Accounting SystemGeorgiboy
2TRK9815789789TRACK9815789789
3TRK9815789474ATRACK9815789474
4TRK9815789BTRACK9815789
5TRK9815789CTRACK9815789
6TRK9815789AATRACK9815789
7TRK98157899945AAATRACK98157899945
8 
9Bank 
10Account:1000, Bank:Bank of England, TRACK98123 CUSTOMER: Accounts PayableTRACK98123
11Account:1000, Bank:Bank of Scotland, Type:Checking TRACK98207998891 Party:Henry LeeTRACK98207998891
Sheet1
Cell Formulas
RangeFormula
B2:B11B2=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")," "),""))
 
Upvote 0
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),""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Extracting text between multiple sets of markers and displaying on separate lines
2
Replies
12
Views
967
Fluff
Fluff
P
  • Question Question
Extracting data from a field within a text
Replies
2
Views
856
Peter_SSs
Peter_SSs
E
  • Question Question
Find and extract text in a cell
Replies
1
Views
403
Eldublee
E
J
  • Locked
  • Question Question
Formula Text String Help - Extract a sequence of numbers/letters from a text string
Replies
1
Views
2K
Fluff
Fluff
R
  • Solved
Extracting numbers from at end of a string
2
Replies
19
Views
9K
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top