I would like to find the location of a cell that contains certain text. For instance, I would like to find cell number of the cell which contains Functionviel,

For example, a cell is: Functionviel : 1.59545e 07 EUR, whose cell number let's say A19845. Then using that I will perform certain operations like: =TRIM(MID($A$19845, 28, 7))*POWER(10,7)

How can I find which cell includes Functionviel?

CodePudding user response：

Using these definitions:

A4:A12 is our array to search. 
B1 has the text you want to find. 

This formula will search your range for the desired text and return the cell address. It's using a wildcard search.

=CELL("address", INDEX($A$4:$A$12,MATCH("*"&$B$1&"*",$A$4:$A$12,0)))

Index Match provides advanced lookup capabilities in Excel.

UPDATE

However, that will only get you the address and not play well within your formula. If you want the actual value (thanks BigBen for pointing that out), then just slightly tweak as:

=TRIM(MID(INDEX($A$1:$A$20000,MATCH("*"&$B$1&"*",$A$1:$A$20000,0)), 28, 7))*POWER(10, 7)

Perhaps the tricky part is how you know to use 28 and 7 in your MID logic. Will that always be the same?

CodePudding user response：

This is a index match combo solution. see

PS I did this as fully exploded formula to see the actions at each step. When you understand what was done you can hide the intermediate columns. You could combine them into a monstrosity of a formula that would be hard to understand in two weeks let alone in two years.