# [SOLVED] copy entire cell if it contains text

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.

C:\fakepath\Sample.ods

edit retag close merge delete

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).

( 2020-08-23 19:06:00 +0100 )edit

Sort by » oldest newest most voted

Hello,

please see the following sample file: C:\fakepath\FindandShow.ods

Prerequites for this to work:

• Don't pre-format cells as text (Format Code: @) if you intend to put formulas into the cells. You will see the formulas as text, but no evaluation will occur.
• Set Tools -> Options -> LibreOffice Calc -> Calculate -> Section: Formula wildcards -> Option: [x] Enable regular expressions in formulas
• There must be a "To_Find" value, if you put a formula into adjacent cell in column 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.

more

It works perfectly. Thank you very much.

( 2020-08-23 20:18:00 +0100 )edit

Then please mark the answer as correct by clicking the check mark icon. Thanks.

( 2020-08-23 22:10:33 +0100 )edit

## Stats

Seen: 51 times

Last updated: Aug 23