Ask Your Question
0

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

C:\fakepath\Sample.ods

edit retag flag offensive close merge delete

Comments

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
1

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

Opaque gravatar image

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

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.

edit flag offensive delete link more

Comments

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

Stats

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

Seen: 51 times

Last updated: Aug 23