To extract a specific text in a string

[liabilityquek]

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]

In B2, copied down :

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

Regards
Bosco

 

[bluesky63]

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

 

[liabilityquek]

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]

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

 

[liabilityquek]

Then,

try this formula instead.

In B2, copied down :

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

View attachment 64941

Thanks Bosco!

 

[shrivallabha]

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.