# To extract a specific text in a string

#### liabilityquek

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

#### bosco_yip

##### Excel Ninja

In B2, copied down :

=MID(A2,SEARCH(" t????? ",SUBSTITUTE(A2,"-"," ")&" ")+1,6)

Regards
Bosco

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

• 11.1 KB Views: 5
Last edited:

#### liabilityquek

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

#### bosco_yip

##### Excel Ninja
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,"-"," "))
Then,

try this formula instead.

In B2, copied down :

=TRIM(MID(A2,FIND("T",A2),6))

Last edited:

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