Search a Cell based on the content and return the position

Discuss the spreadsheet application

Search a Cell based on the content and return the position

Postby gutee » Fri Jan 18, 2019 9:24 am

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.
OpenOffice.org.3.3.0
gutee
 
Posts: 19
Joined: Wed Jan 09, 2019 9:12 am

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

Postby Zizi64 » Fri Jan 18, 2019 9:54 am

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.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8486
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby gutee » Fri Jan 18, 2019 11:04 am

thanks, I will use for loop just make it work at first
OpenOffice.org.3.3.0
gutee
 
Posts: 19
Joined: Wed Jan 09, 2019 9:12 am

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

Postby keme » Fri Jan 18, 2019 11:21 am

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.
User avatar
keme
Volunteer
 
Posts: 3271
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby Lupp » Fri Jan 18, 2019 3:43 pm

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.
Attachments
demoDimensionLine_1.odg
(13.25 KiB) Downloaded 10 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2562
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 35 guests