To copy value in A2 (only if A2 contains lib any where in A2) to cell containing formula, B2, I have tried to use;
=if(A2=lib,A2)
=if(A2=‘lib’,A2)
=if(A2=“lib”,A2)
=if(A2=libA2)
and many others
I have scoured the CALC book and do not see a wildcard or anything that I can use.
Any ideas ?
Thanks,
⌡im [THE BookMan]
Try with =IF(FIND("lib";A2)>=1;A2).
Excellent !
Worked great!
Please explain '>=1;"
I don’t quite see what those do …
Thank you.
Hallo
Find(…) returns the Position of the found, otherwise an Error,
therefore I would suggest
=IFERROR(IF(FIND("lib";A2);A2);"")
1 Like
Another wonderful addition to my list of expressions.
TNX
You could test the REGEX-function on future occaisions, when a simple find would not work.
https://help.libreoffice.org/latest/en-US/text/scalc/01/func_regex.html?DbPAR=CALC#bm_id831542233029549
.
Also there is a setting in options for calc to allow regular expressions or (newer option) wildcards in arguments. If I’m remembering right the function SEARCH allows switching on regular expressions, while FIND doesn’t.
https://help.libreoffice.org/latest/en-US/text/scalc/01/04060110.html?DbPAR=CALC#bm_id3151005
2 Likes
Sorry! When I wrote my answer I obviously hadn’t read your comment.
So, if A2 contains LIB,
=IFERROR(IF(FIND("lib";A2);A2);"") would show nothing, but =IFERROR(IF(SEARCH("lib";A2);A2);"") would answer LIB.
The function SEARCH is not case-sensitive.
A solution with a concise formula and with high flexibility concerning modifications (case sensitivity or restrictions regarding the position of the searched string inside the scanned string e.g) you can get with the help of the REGEX() function (LibreOffice V6.2 or higher: Thanks to @ErAck!) . See attached demo:
disask84549selectivePullWithRegex.ods (15.5 KB)
1 Like