Ask Your Question

[SOLVED] copy entire cell if it contains text

asked 2020-08-23 18:43:34 +0100

zm23 gravatar image

updated 2020-08-23 20:17:29 +0100

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.

image description

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.


edit retag flag offensive 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).

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

1 Answer

Sort by » oldest newest most voted

answered 2020-08-23 19:14:58 +0100

Opaque gravatar image

updated 2020-08-23 20:01:33 +0100


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.


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:; 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.

edit flag offensive delete link more


It works perfectly. Thank you very much.

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

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

erAck gravatar imageerAck ( 2020-08-23 22:10:33 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-23 18:43:34 +0100

Seen: 51 times

Last updated: Aug 23