Press "Enter" to skip to content

Basics of Find, Replace in LibreOffice Calc

Last updated on February 20, 2020

This article explains how to perform basic search and replace in a Calc spreadshet.

Find

Spreadsheet may contains thousands of data. Find or Search functions used to locate a string/text in a spreadsheet. You can find option in Calc Menu : Edit -> Find.

Calc - Menu - Find

After clicking the Find from menu, the main dialog appears at the bottom of the Sheet with a textbox where you can type the text you want to search. Then hit the Find Next (down arrow icon).

As a result, you can see the first hit is highlighted/ selected in the sheet if the text is found.

Calc- Single Find - Next

If you keep clicking the Find, Calc would show all the matching strings one-by-one.

Find All

A text may appear multiple places in a sheet. The Find All button searches for all matching texts together and highlights them. Calc also shows a popup dialog with the list of cells numbers where it found the text. Find all also searches multiple sheets for the same text and popup dialog shows the sheet name as well.

Calc - Find All

Replace

Replacing a text is easy in Calc. Open the Find and Replace dialog from the Menu: Edit -> Find and Replace.

SEE ALSO:  How to Add Numeric Data Validation in Calc Spreadsheet

Calc - Menu - Find and Replace

The find a replace dialog shows two main text box. Enter the text you want to find in the Find text box. And enter the text which you want to be replaced with in the replace text box.

As a result of clicking the Replace All, all the matched text from find text box would be replaced with the replace text.

Calc- Replace All

 

Find and Replace in a Selected Range

LibreOffice Calc find and replace dialog searches and replaces strings in entire sheet. However, it is often needed to perform find and replace in a selected ranges but not in entire sheet. Here’s how you can do it:

Select a range where you want to find a text and replace.

Open the Find and Replace dialog (CTRL+H). Provide Find and Replace string. 

Under ‘Other options’, select “Current Selection Only” and press replace all.

Now you can see only the strings matching “East” inside the selection is replaced. Other non-selected cells strings remains untouched.

Drop a comment if you are facing difficulties or any suggestions.

 

Next Chapter Using Templates


Looking for something else?

Read our complete tutorial index of Calc, Writer, Impress and Draw.


Related


6 Comments

  1. Debra A Overbey Debra A Overbey December 14, 2017

    It appears that it is NOT possible to find and replace all within a SELECTED RANGE OF CELLS ONLY? It appears that one can only either: replace one matching cell at a time or replace all matching cells on the sheet – but not specific cells that are within a highlighted range?

    • arindam arindam Post author | December 15, 2017

      It is possible to do search and replace in a highlighted range. Please see above, the article is updated. Thanks.

  2. Debra A Overbey Debra A Overbey December 15, 2017

    Thank you!!!

  3. Mark Aitchison Mark Aitchison February 28, 2018

    It looks like there is no way to search multiple spreadsheet files in a directory with one search, is this correct?

    • arindam arindam Post author | March 3, 2018

      You have to use Macros (Advanced) to search a string in multiple spreadsheets in a single directory. With basic options it is not available.

  4. PSi PSi September 23, 2020

    I have cells that have two space-separated sets of three numbers:

    657 691
    657 692
    657 693
    691 657
    692 657
    693 657

    In order to keep separation (and not to mis-interpreted it as thousand’s separator I have to put single quote mark in front of the cell text:

    ‘657 691
    ‘657 692

    The single quote naturally does not show on the cell.

    I now need to replace the 657 with 659. How to do it with Search&replace?

    When I try it, I’ll loose the single quotation mark AND the space in between the number parts. If I copy-paste the strings into external editor (Notepad++) and replace the numbers there I cannot copy-paste the strings back withot loosing the space in between. If I add the single quote at the external editor, it becomes a visible character at LibreOffice.

    Using RegEx did not seem to work: There is a leading OR trailing space at the string to be searched, but replacing (\s?)657(\s?) with $1659$2 wont work either.

    What is the correct way of replacing part of string that only contains two sets of three numbers with a space in between — in my case: string 657 with 657?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.