Formula for address of a part of text

I have a column like this:

. A
1 A: B 114 TRA - IF 5137
2 R: B 115 TRA - IF 5143
3 A: B 81 GTZ
4 R: B 87 XMV
5 A: B 101 TRA - IF 5139
6 R: B 102 TRA - IF 5136
7 A: CJ 81 TRA
8 R: B 109 TRA - IF 5142


I need a formula who search in column A (A:A) a part of text (exp:"B 101 TRA") and return in Sheet2 it address ==> (Sheet1!A3)


Thanks,

hi,

I dont think you can search a part of text using excel formula. You can use macro for achieving this.

Regards,
Sindhu

I find it

For exemple in cell B1 (Sheet2) I type B 101 TRA
In cell B2 (Sheet2) formula : =ADDRESS(MATCH(CONCATENATE("*",B1,"*"),Sheet1!$A:$A,0),2,4)



Anyway, thanks for the help !

Great solution. I never knew this before. Good learning