Ask Your Question

Search for multiple characters at once using Find & Replace? [closed]

asked 2019-09-08 11:07:04 +0200

appreciatethehelp gravatar image

updated 2020-08-05 01:00:03 +0200

Alex Kemp gravatar image

Hi all, I'm using Find & Replace at the moment to locate/select certain cells in a Calc document so that I can apply a Cell Style to them all at once. However it would be helpful if I could search for a few numbers at once, rather than each one individually; how do I do this? I can't find anything on this in the Startup Guide...

I've tried displaying multiple numbers in the Find field like this: 1 2 3 4/1,2,3,4/ 1, 2, 3, 4/1;2;3;4/ 1:4 etc. but Find & Replace doesn't recognise them as separate numbers, instead generating a "no results found" response.

As always, cheers.


1) Follow Opaques' answer below to find cells that contain the specified characters anywhere in each cell e.g. if the characters specified in your search are 1 & 2, any cells containing these characters will be highlighted e.g. cells containing characters A1, 28 etc.

2) Follow his/her subsequent comment to find cells that contain the exact characters specified e.g.if the characters specified in your search are 1 & 2, only cells containing exactly 1 or 2, and nothing else, will be highlighted by the search.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by appreciatethehelp
close date 2019-09-08 18:17:25.584419

1 Answer

Sort by » oldest newest most voted

answered 2019-09-08 11:19:42 +0200

Opaque gravatar image

updated 2019-09-08 18:06:18 +0200


If you want a range of digits to be found, use [pattern].

Example - you want to find all cells containing at least one out of the digits 1 to 4 (i.e one of 1,2,3,4) use [1-4] (don't forget to tick option Regular expressions). For more details on regular expression see ICU Regexp, which is used in LibreOffice.

Note: In this case [1-4] is also a short version of [1,2,3,4] (Now it is easy to adapt to other question like Show me all cells containing A or B)

See the following simple example (after having pressed Find All dialog Search Results is shown and all matching cells are highlighted)

image description

Have a nice trip to the world of regular expressions!

Update 1 for exact matching: Use regexp ^[1-4]$ in Find

edit flag offensive delete link more


This is all very valuable information; I will be sure to investigate Regular Expressions in further detail. However in this particular instance I am hoping to search for the specific numbers 1 to 4, not cells that contain these numbers in their characters somewhere, but cells that contain EXACTLY THESE NUMBERS AND NO OTHERS e.g. I would like a cell containing 1 or 4 to turn up in the results, but not a cell containing 14 or 41. How do I modify my search to meet these parameters?


appreciatethehelp gravatar imageappreciatethehelp ( 2019-09-08 12:13:52 +0200 )edit

I appreciate that - being unspecific in question, waiting for an answer, being unsatisfied and specifying in a comment what was already known being a condition while writing the question.

Use ^1$|^2$|^3$|^4$ for finding exactly 1, 2, 3 or 4 -- or
use ^[1-4]$

Opaque gravatar imageOpaque ( 2019-09-08 12:25:42 +0200 )edit

Yes, I should have been more specific; apologies, and thanks for your patient response.

Your advice is sound. Just one last issue, if I may: entering a double-digit number into the search field seems to ruin the calculation; the formula ^[7-9]$ works just fine, however ^[7-10]$ doesn't.... any ideas on how to help LO identify the two-digit number as one number?

appreciatethehelp gravatar imageappreciatethehelp ( 2019-09-08 16:39:43 +0200 )edit

seven to 9 + 10 should end up in ^[0-9]$|^10$ - Try to understand [1-8] means any single digit from 1 to 8 and is not a range specification of integers in the sense of any integer between 1 and eight. There are only 10 digits - from 0 to 9 and 10 is not a single digit, but a number composed of 2 digits. Understanding that immediately makes clear that ^[7-10]$ does not mean *any number between 7 and 10.

To develop a working regular expression you must clearly define you conditions in advance. It could be pretty challenging to adapt a regular expression afterwards. Example: If you now start to define I'd like to find all numbers having one or two digits - my answer would be ^[:digit:]{1,2}$ which looks quite different then anything else until now.

Opaque gravatar imageOpaque ( 2019-09-08 16:54:16 +0200 )edit

Understood, thanks for taking the time to explain that. Clearly I have a fair bit of reading to do on the subject; advice on a good starting point would be great, but I'm surely pushing my luck at this point!

appreciatethehelp gravatar imageappreciatethehelp ( 2019-09-08 17:10:09 +0200 )edit

To recommend something is always a bit difficult for me, since leanings paths are quite individual. And Regular Expression is a bit more specific, since there is not THE regular expression specification and implementation but there are many of them (That's why I linked above to ICU and mentioned that this is what LibreOffice implements). May be you find this PDF useful (but searching for regular expression tutorial on the internet yields plenty of other results.

Opaque gravatar imageOpaque ( 2019-09-08 17:24:49 +0200 )edit

Above and beyond! Legend.

appreciatethehelp gravatar imageappreciatethehelp ( 2019-09-08 18:13:36 +0200 )edit
LeroyG gravatar imageLeroyG ( 2020-11-10 18:11:51 +0200 )edit

Question Tools

1 follower


Asked: 2019-09-08 11:07:04 +0200

Seen: 690 times

Last updated: Sep 08 '19