Page 1 of 1

Search a Cell based on the content and return the position

PostPosted: Fri Jan 18, 2019 9:24 am
by gutee
there is a Cell in my spreadsheet, its content is a keyword string, are there any method available to search it return its position.
use one for loop to access each cell and check the string may not complex, but that will not good enough.
Thanks.

Re: Search a Cell based on the content and return the positi

PostPosted: Fri Jan 18, 2019 9:54 am
by Zizi64
use one for loop to access each cell and check the string may not complex, but that will not good enough.


All of the softwares, predefined built-in functions will work based on same or similar method.

Re: Search a Cell based on the content and return the positi

PostPosted: Fri Jan 18, 2019 11:04 am
by gutee
thanks, I will use for loop just make it work at first

Re: Search a Cell based on the content and return the positi

PostPosted: Fri Jan 18, 2019 11:21 am
by keme
If you want to locate that string (keyword) in a different cell, and the range where it may be located is a range with single row or column, you can use the MATCH() function. It supports regular expressions.

If you want to locate the starting position of a particular keyword within that string, you can use SEARCH() or FIND(). The two functions are similar, but behave differently with respect to regular expressions and case sensitivity.

Re: Search a Cell based on the content and return the positi

PostPosted: Fri Jan 18, 2019 3:43 pm
by Lupp
Searching ranges for cells with a specific content (or othertwise meeting a codition) is done using MATCH() basically, but this is restricted to ranges being parts of single columns or of single rows as already mentioned.
This should generally be sufficient, because such a search is made for DATA of a specific kind, and DATA possibly having a value searched for should only occur under the respective data label (field), and thus in ONE column (mostly; exceptionally: a row). If spreadsheets are used for a kind of data keeping they should also regard well proven principles of data keeping.

A two-dimensional search for contents does not occur then. To need it will mostly mean that basic principles were disregarded.

If you are searching a contigency table, on the other hand, you need to realise that a specific content may occur more often than once. Standard MATCH always returns the index of first occurrence. In the 2D-case this concept is ambiguos: Shall the "first" occurrence be the one in the topmost row of any occurrence, or the one in the leftmost column?

In fact I would assume, a solution, if urgently needed, is best done resorting to user code.
Solutions based on standard functions are tricky and therefore error-prone.
You may have a look into the attached example, however. It is showing the principle of ONE of a few thinkable approaches.
You need to understand "array-evaluation" if you want to make use of the example.