How to write formula:
search in Column A for the content of cell B2 and if there is a match copy entire cell from column A to C2.
The problem might be that values to find may contain some not typical charackters like “:” or “/” or “__” and both text and numbers but generally they may be treated as text data.
Basically I have a database of paths to files and if I type file name I want Calc to find and copy that path and return something like “not found” or whatever if there is no match.
copy entire cell from column A to C2
Nitpicking: Formulas cannot copy any content. Formulas reference the values of other cells. To perform a real copy you need user code (macro).
Hello,
please see the following sample file: FindandShow.ods
Prerequites for this to work:
@) if you intend to put formulas into the cells. You will see the formulas as text, but no evaluation will occur.Tools -> Options -> LibreOffice Calc -> Calculate -> Section: Formula wildcards -> Option: [x] Enable regular expressions in formulas
C. If you enter the formula in column C without a search criterion, you’ll get the first entry of column A (Where). You’d need to make a more complex formula to deal with this.Formula
The formula in C2 is: =IF(ISNA(MATCH(".*"& B2 &".*";A$2:A$1000;0));"not found";OFFSET($A$1;MATCH(".*"& B2 &".*";A$2:A$1000;0);0;1;1))
Note The formula will find the first match in case of ambiguous search criteria in "To_Find" column.
Tested using LibreOffice:
Version: 7.0.0.3; Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: en-US (en_US.UTF-8); UI: en-US; Calc: threaded
Hope that helps.
It works perfectly. Thank you very much.
Then please mark the answer as correct by clicking the check mark icon. Thanks.