• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To extract a specific text in a string

liabilityquek

New Member
Based on the example below:

How do i extract the specific text of "T17017" for a string? I could use text to column and delimiter by width but I would need to sort it out. Is there a formula to extract out the specific text?

Adjustment due to wrong cost centre-T17017 (Dec-Fe
Adjustment due to wrong cost centre-T17017 (Dec-Fe
Accrual for T17017 Mar 19 Admin Fee

Reclass of CC for Admin fee For T17017
Reclass of CC for Admin fee For T17017
Accrual for T17017 Mar 19 Admin Fee

 

bluesky63

Member
Hi another method
=IFERROR(LOOKUP(2^15,SEARCH(Table1[Search Word],A2),Table1[Return]),"NA")

You can build your search lookup in the right hand table

Note:- Sort in Ascending order for the Search column
 

Attachments

Last edited:

liabilityquek

New Member
Hi Bosco

The formula works great!

If i have another variable:
Accrual for T1701 Mar 19 Admin Fee

How should i go about it? How can i input another search(" t???? ",substitute(A2,"-"," "))
 

shrivallabha

Excel Ninja
A little safer construct would be to use
=MID(A1,MIN(FIND("T"&{0,1,2,3,4,5,6,7,8,9},A1&"T"&{0,1,2,3,4,5,6,7,8,9},1)),6)
as it will test for a capital T which precedes a numeral.
 
Top