How to use Find and Replace in Excel most efficiently

In this tutorial, you will learn how to use Find and Replace in Excel to search for specific data in a worksheet or workbook, and what you can do with those cells after finding them. We will also explore the advanced features of Excel search such as wildcards, finding cells with formulas or specific formatting, find and replace in all open workbooks and more.

When working with big spreadsheets in Excel, it's crucial to be able to quickly find the information you want at any particular moment. Scanning through hundreds of rows and columns is certainly not the way to go, so let's have a closer look at what the Excel Find and Replace functionality has to offer.

How to use Find in Excel

Below you will find an overview of the Excel Find capabilities as well as the detailed steps on how to use this feature in Microsoft Excel 365, 2019, 2016, 2013, 2010 and older versions.

Find value in a range, worksheet or workbook

The following guidelines tell you how to find specific characters, text, numbers or dates in a range of cells, worksheet or entire workbook.

  1. To begin with, select the range of cells to look in. To search across the entire worksheet, click any cell on the active sheet.
  2. Open the Excel Find and Replace dialog by pressing the Ctrl + F shortcut. Alternatively, go to the Home tab > Editing group and click Find & Select > Find
    Opening Excel's Find and Replace dialog
  3. In the Find what box, type the characters (text or number) you are looking for and click either Find All or Find Next.
    In the Find what box, type the characters you are looking for.

When you click Find Next, Excel selects the first occurrence of the search value on the sheet, the second click selects the second occurrence, and so on.

When you click Find All, Excel opens a list of all the occurrences, and you can click any item in the list to navigate to the corresponding cell.
Excel's Find All results

Excel Find - additional options

To fine-tune your search, click Options in the right-hand corner of the Excel Find & Replace dialog, and then do any of the following:

  • To search for the specified value in the current worksheet or entire workbook, select Sheet or Workbook in the Within.
  • To search from the active cell from left to right (row-by-row), select By Rows in the Search To search from top to bottom (column-by-column), select By Columns.
  • To search among certain data type, select Formulas, Values, or Comments in the Look in.
  • For a case-sensitive search, check the Match case check.
  • To search for cells that contain only the characters you've entered in the Find what field, select the Match entire cell contents.

Additional options of Excel Find

Tip. If you want to find a given value in a range, column or row, select that range, column(s) or row(s) before opening Find and Replace in Excel. For example, to limit your search to a specific column, select that column first, and then open the Find and Replace dialog.

Find cells with specific format in Excel

To find cells with certain formatting, press the Ctrl + F shortcut to open the Find and Replace dialog, click Options, then click the Format… button in the upper right corner, and define your selections in Excel Find Format dialog box.
Finding cells with specific format in Excel

If you want to find cells that match a format of some other cell on your worksheet, delete any criteria in the Find what box, click the arrow next to Format, select Choose Format From Cell, and click the cell with the desired formatting.
Finding cells that match a format of another cell

Note. Microsoft Excel saves the formatting options that you specify. If you search for some other data on a worksheet, and Excel fails to find the values that you know are there, clear the formatting options from the previous search. To do this, open the Find and Replace dialog, click the Options button on the Find tab, then click the arrow next to Format.. and select Clear Find Format.

Find cells with formulas in Excel

With Excel's Find and Replace, you can only search in formulas for a given value, as explained in additional options of Excel Find. To find cells that contain formulas, use the Go to Special feature.

  1. Select the range of cells where you want to find formulas, or click any cell on the current sheet to search across the entire worksheet.
  2. Click the arrow next to Find & Select, and then click Go To Special. Alternatively, you can press F5 to open the Go To dialog and click the Special… button in the lower left corner.
    Open the Go To Special dialog.
  3. In the Go To Special dialog box, select Formulas, then check the boxes corresponding to the formula results you want to find, and click OK:
    • Numbers - find formulas that return numeric values, including dates.
    • Text - search for formulas that return text values.
    • Logicals - find formulas that return Boolean values of TRUE and FALSE.
    • Errors - find cells with formulas that result in errors such as #N/A, #NAME?, #REF!, #VALUE!, #DIV/0!, #NULL!, and #NUM!.

    Check the boxes corresponding to the formula results you want to find.

If Microsoft Excel finds any cells that meet your criteria, those cells are highlighted, otherwise a message will be displayed that no such cells have been found.

Tip. To quickly find all cells with formulas, regardless of the formula result, click Find & Select > Formulas.

How to select and highlight all found entries on a sheet

To select all occurrences of a given value on a worksheet, open the Excel Find and Replace dialog, type the search term in the Find What box and click Find All.

Excel will display a list of found entities, and you click on any occurrence in the list (or just click anywhere within the results area to move the focus there), and press the Ctrl + A shortcut. This will select all found occurrences both on the Find and Replace dialog and on the sheet.
Select all found entries on a sheet

Once the cells are selected, you can highlight them by changing the fill color.

How to use Replace in Excel

Below you will find the step-by-step guidelines on how to use Excel Replace to change one value to another in a selected range of cells, entire worksheet or workbook.

Replace one value with another

To replace certain characters, text or numbers in an Excel sheet, make use of the Replace tab of the Excel Find & Replace dialog. The detailed steps follow below.

  1. Select the range of cells where you want to replace text or numbers. To replace character(s) across the entire worksheet, click any cell on the active sheet.
  2. Press the Ctrl + H shortcut to open the Replace tab of the Excel Find and Replace dialog.

    Alternatively, go to the Home tab > Editing group and click Find & Select > Replace
    Open the Replace tab of the Excel Find & Replace dialog.

    If you've just used the Excel Find feature, then simply switch to the Replace tab.

  3. In the Find what box type the value to search for, and in the Replace with box type the value to replace with.
  4. Finally, click either Replace to replace the found occurrences one by one, or Replace All to swap all the entries in one fell swoop.
    Click Replace to swap the found occurrences one by one, or Replace All to swap all the entries at a time.
Tip. If something has gone wrong and you got the result different from what you'd expected, click the Undo button or press Ctrl + Z to restore the original values.

For additional Excel Replace features, click the Options button in the right-hand corner of the Replace tab. They are essentially the same as the Excel Find options we discussed a moment ago.

Replace text or number with nothing

To replace all occurrences of a specific value with nothing, type the characters to search for in the Find what box, leave the Replace with box blank, and click the Replace All button.
Replace a specific value with nothing.

How to find or replace a line break in Excel

To replace a line break with a space or any other separator, enter the line break character in the Find what filed by pressing Ctrl + J. This shortcut is the ASCII control code for character 10 (line break, or line feed).

After pressing Ctrl + J, at first sight the Find what box will look empty, but upon a closer look you will notice a tiny flickering dot like in the screenshot below. Enter the replacement character in the Replace with box, e.g. a space character, and click Replace All.
Replacing all line breaks on the sheet with spaces

To replace some character with a line break, do the opposite - enter the current character in the Find what box, and the line break (Ctrl + J) in Replace with.

How to change cell formatting on the sheet

In the first part of this tutorial, we discussed how you can find cells with specific formatting using the Excel Find dialog. Excel Replace allows you to take a step further and change the formatting of all cells on the sheet or in the entire workbook.

  • Open the Replace tab of Excel's Find and Replace dialog, and click the Options
  • Next to the Find what box, click the arrow of the Format button, select Choose Format From Cell, and click on any cell with the format you want to change.
  • Next to the Replace with box, either click the Format… button and set the new format using the Excel Replace Format dialog box; or click the arrow of the Format button, select Choose Format From Cell and click on any cell with the desired format.
  • If you want to replace the formatting on the entire workbook, select Workbook in the Within box. If you want to replace formatting on the active sheet only, leave the default selection (Sheet).
  • Finally, click the Replace All button and verify the result.

Replacing cell formatting on the entire sheet.

Note. This method changes the formats applied manually, it won't work for conditionally formatted cells.

Excel Find and Replace with wildcards

The use of wildcard characters in your search criteria can automate many find and replace tasks in Excel:

  • Use the asterisk (*) to find any string of characters. For example, sm* finds "smile" and "smell".
  • Use the question mark (?) to find any single character. For instance, gr?y finds "Gray" and "Grey".

For example, to get a list of names that begin with "ad", use "ad*" for the search criteria. Also, please keep in mind that with the default options, Excel will search for the criteria anywhere in a cell. In our case, it would return all the cells that have "ad" in any position. To prevent this from happening, click the Options button, and check the Match entire cell contents box. This will force Excel to return only the values beginning with "ad" as shown in the below screenshot.

Using wildcard characters in search criteria

How to find and replace wildcard characters in Excel

If you need to find actual asterisks or question marks in your Excel worksheet, type the tilde character (~) before them. For example, to find cells that contain asterisks, you would type ~* in the Find what box. To find cells that contain question marks, use ~? as your search criteria.

This is how you can replace all questions marks (?) on a worksheet with another value (number 1 in this example):

Replacing all questions marks on a worksheet with number 1

As you see, Excel successfully finds and replaces wildcards both in text and numeric values.

Tip. To find tilde characters on the sheet, type a double tilde (~~) in the Find what box.

Shortcuts for find and replace in Excel

If you have been closely following the previous sections of this tutorial, you might have noticed that Excel provides 2 different ways to interact with Find and Replace commands - by clicking the ribbon buttons and by using the keyboard shortcuts.

Below there is a quick summary of what you've already learned and a couple more shortcuts that may save you a few more seconds.

  • Ctrl+F - Excel Find shortcut that opens the Find tab of the Find & Replace
  • Ctrl+H - Excel Replace shortcut that opens the Replace tab of the Find & Replace
  • Ctrl+Shift+F4 - find the previous occurrence of the search value.
  • Shift+F4 - find the next occurrence of the search value.
  • Ctrl+J - find or replace a line break.

Search and replace in all open workbooks

As you have just see, Excel's Find and Replace provides a lot of useful options. However, it can search only in one workbook at a time. To find and replace in all open workbooks, you can use the Advanced Find and Replace add-in by Ablebits.

The following Advanced Find and Replace features make search in Excel even more powerful:

  • Find and Replace in all open workbooks or selected workbooks & worksheets.
  • Simultaneous search in values, formulas, hyperlinks and comments.
  • Exporting search results to a new workbook in a click.

To run the Advanced Find and Replace add-in, click on its icon on the Excel ribbon, which resides on the Ablebits Utilities tab > Search group. Alternatively, you can press Ctrl + Alt + F, or even configure it to open by the familiar Ctrl + F shortcut.
Run the Advanced Find and Replace add-in by clicking on its icon.

The Advanced Find and Replace pane will open, and you do the following:

  • Type the characters (text or number) to search for in the Find what
  • Select in which workbooks and worksheets you want to search. By default, all sheets in all open workbooks are selected.
  • Choose what data type(s) to look in: values, formulas, comments, or hyperlinks. By default, all data types are selected.

Additionally, you have the following options:

  • Select the Match case option to look for case-sensitive data.
  • Select the Entire cell check box to search for exact and complete match, i.e. find cells that contain only the characters you've typed in the Find what

Type text or numbers to search for and configure additional options if needed.

Click the Find All button, and you will see a list of found entries on the Search results tab. And now, you can replace all or selected occurrences with some other value, or export the found cells, rows or columns to a new workbook.
Replace all or selected occurrences, or export the found cells, rows or columns to a new workbook.

If you are willing to try the Advanced Find and Replace on your Excel sheets, you are welcome to download a fully functional trial version.

I thank you for reading and hope to see you on our blog next week. In our text tutorial, we will dwell on Excel SEARCH, FIND and REPLACE functions, so please keep watching this space.

You may also be interested in

157 comments to "How to use Find and Replace in Excel most efficiently"

  1. Chandra Mohan says:

    Hi Good Evening

    i would like replace 1st and 2nd positions in CELL with Nothing

    for example i have phone Numbers like
    which ever the Cell Consist 1st positing has 9 2nd postions as 1 need to be replaced with Nothing
    Final results should come like


    PLease Suggest the Formula

  2. Thibaud says:

    Hi, I have a large list of telephone numbers in different formats. Here's some made up examples


    Notice that in my example, the numbers are all the same except some have a telephone prefix (always two digits). In my actual list, there is some times a + preceding the prefix, but usually not.

    I would like for all the telephone numbers to appear like

    How would I do that?

    If I want to change 32475851753 to +32475851753 I would enter the following in find: 32********* but then I don't know of any way to replace properly.

  3. hara says:

    Hi..can i know how to replace word/item in different excel in same time...its hard for me to open the files n replace the same word n took long time to finish it... fyi im use excel 2013

  4. Asim Rafique says:

    Hello there,
    Dear I wan to write a range of some numbers in single sell, eg From "01 to 3.0" instead of 1,1.1,1.2,1.3 .... & so on
    "3.0 to 6.0" instead of "3.0,3.1,3.2,3.3 ............ 5.8,5.9,6.0"

    kindle help me to find the solution

  5. Asim Mirza says:

    Hello there,

    dear concerned ! I want to add range in single cell how can I do that, EG I want to add (0.1 to 3.0) instead of adding " 0.1, 0.2, 0.3 ----- 2.7,2.8,2.9,3.0 " Can you Please help me with it.

    Asim Rafique

  6. Asim Mirza says:

    Hello there,
    Dear I wan to write a range of some numbers in single sell, eg From "01 to 3.0" instead of 1,1.1,1.2,1.3 .... & so on
    "3.0 to 6.0" instead of "3.0,3.1,3.2,3.3 ............ 5.8,5.9,6.0"

    kindle help me to find the solution

  7. Chanel says:

    Hi Mike!

    I cannot seem to find anything to help me .

    I have data sorted into street address, city, state, zip
    I know how to connect them all
    but the street address is often written such
    123 Main st instead of 123 Main Street,
    555 lisbon Rd. instead of 555 Lisbon Road

    is there a function for me to be able to adjust just the end of the cell which consists the error?
    I do have 167 values to be correcting automatically. just don't know which formula to use..

  8. saai says:

    My excel sheet is updated automatically using formula. I want to search a value in that formula using find option. How to do that?

  9. Farrukh Amin says:

    Is there any method or function in excel where I can replace a number (say a digit) on an alternative occurrence? For example the following data
    I want to replace first, third, fifth, and so on. 2 with 22, Not all the 2s with 22
    Or wherever it appears 2 in the column, it should replace one 2 with 22 and leave the 2nd one, similarly, replace the next w with 22, but not the next one immediately after this replacement.

  10. SAM GAFFNEY says:

    I need to strip an email address of the name and "@" character and leave the existing domain name.
    example sam.g needs to become just
    Suggestions please.

  11. Bernard Rothschild says:

    My find and replace feature in Excel does not always function. I went into options and clicked the arrow
    but clear find format will not open. It appears in very light letters on screen. How can I make it
    function in an effort to make find and replace work properly.
    this is the initial time I posted a question in this environment. If it has been answered somewhere
    tell me how I can find it.

  12. Bernard RothschildMy find and replace feature in Excel does not always func says:

    My find and replace feature in Excel does not always function. I went into options and clicked the arrow
    but clear find format will not open. It appears in very light letters on screen. How can I make it
    function in an effort to make find and replace work properly.
    this is the initial time I posted a question in this environment. If it has been answered somewhere
    tell me how I can find it.

  13. Sayan Dey says:

    I want to remove the first special character and keep the rest of the string. ex- ; NDC Code(s): 73309-217-01; Packager: BLUEBAY SHANDONG CO.,LTD; Category: BULK INGREDIENT; DEA Schedule: None; Marketing Status: Bulk Ingredient For Animal Drug Compounding.

    So here I want to remove " ; " this special character. only the first one and keep rest of the string. apart from python how to do this on excel?

    Thank you in advance

      • SAM says:

        Thank you in advance for you consideration. I figured it out and it was much simpler than I was making it.

        Find: "*@"
        Replace "" (nothing)

        This worked but I still do not understand how to keep some of the original text in the replace option.

  14. Joe Larobina says:

    In a filtered search, when wanting to replace blank cells, with text in a column, it also replaces all blank cells not visible in the filtered search.

    Is there a way to only replace blank cells within the filtered search?

    Thank you in advance.

    • Sayan Dey says:

      You can. select the first value and then shift+ last cell (or ctrl + shift + end) of the filtered search. Then click the find and replace and do your thing.

  15. Joshua says:

    I have a list of prices in a column for example:
    How do I change the last 3 digits of each one to 151 so that in that column it now says:

  16. Fredrik Hyltén-Cavallius says:

    Hello if I have 01-HG-1111-1 and I want it to be 01-HG1111-1 how do I do? In the same column there is other tags as 02-HG-2222 as I want to be like 02-HG2222

  17. Melanie Adamik says:

    Is there anyway to find a name example "Smith" and lets say in the find and replace command in excel it finds 3,000 "Smith"s. Is there a way to copy and paste directly from the find and replace window to another sheet or workbook so I don't individually have to do it every time?

  18. Abdul Qadeer says:

    Many thanks for your useful tips.

    Can I ask that is there anyway to bold some part of the sentence including formula such as =““( “&A2&” ) ABCDEDGHIJKLMNOP.”

    Now I want to bold words from L to P. What and where should be added in the formula plz?

    • Joaquin Fonseca says:

      Kind of what I want to do: out of a cell text, use the "Find and Replace" to replace some of the text with bold text. I am sure I have done it before, but now I get the whole text of the cell bold. Notice that the cell value is not a formula, just text.
      Any idea on how to accomplish that (without formulas)? (only change the format of a word or words that are found in a cell value).

  19. Patrick says:

    I have multiple worksheets with various tables containing a variety of data. The (CNTL + F) search box works great for what I am trying to accomplish but I want to have that functionality without having to press (CNTL + F) each time. My ideal solution would result in having the same functionality but in a cell. So is there anyway to have the search box automatically start and appear in the same cell on my first worksheet? Or be able to type search criteria in a cell and have the same functionality as the default search box?

    • Hello Patrick!
      A search box can be automatically displayed with the help of VBA.However, it is unlikely to be reasonable and convenient.
      To give you some advice on how to insert search criteria in separate cells and what formulas to use, more information on your task is needed.
      Please describe your problem in more detail and I will try to help you create a formula you need.

  20. Tam says:

    How do I select hundreds of telephone numbers and change the format from 0499807123 to 0499 807 123 I need to add spacings to lots of telephone numbers. The numbers are in one cell per number.

    • Hello Tam!
      If it is necessary to insert spaces into a number, I recommend applying this formula:

      =TEXT(A1,"0### 000 000")

      If it is necessary to change the number format without changing the number itself, Custom Format should be used.
      Please go to Format Cells, choose Number -> Custom Format and set

      0### ### ###

      I hope it’ll be helpful.

  21. Steven Schultz says:

    Is there a way to modify the contents of a cell, during a macro/scripted import process from one page to another ?

    I need to import products from a database into a 2nd page for editing, but have the spaces removed from between the words and replaced with a "-".
    Currently on Page2, I have "=(Page1!B1)" to import the data, but I cannot use the Find/Replace command on the same cell.

    ie ... Have this work in a single command .. Copy Cell1 Data : Replace " " with "-" : Paste Cell2 Data ..

    Cell1,Page1: Name Of Product Goes Here
    Cell2,Page2: Name-Of-Product-Goes-Here

    Does this make sense ??? lol

    • Hello Steven!
      The standard Excel commands do not allow to change symbols when copying the data. You should first copy the contents of a cell, and then modify it.
      However, we can offer you an out-of-the-box solution to your task – the Convert Text tool included in our Ultimate Suite for Excel.
      This tool will let you change symbols in your cells and create a backup of your original data.
      As the result, you will get two tables:
      1 – with the source data,
      2 – with the modified data.
      Feel free to install Ultimate Suite in a trial mode and test the tools for 30 days for free:

  22. Juliette Brown says:

    I want to replace
    01:01/162:01 which is text
    01:01 as text.
    when I use REPLACE the replacement is a long number in CUSTOM format
    I have tried choosing text format for find and replace with the same result!
    any help would be appreciated

  23. Neil says:

    Hello- is there some syntax that would make it possible to find (CNTL+F) cells that contain any of several words or characters, like any cell that contains "hydrogen" OR "cesium" OR "phthalate"? It would find "hydrogen cyanide" and "iron phthalate". Thanks.

  24. Mike Duran says:

    I am trying to replace:
    10 number format to 010 which I assume in text format. I don't think excel accepts the format and number change. But I need the three digit code.

    Thank you,
    Mike D.

  25. venkatesh says:

    how to sort the data having a particular letter at particular place in a column
    Ex: ramesh233
    i want to sort the letter 'm' at 3rd place. Is there any way in excel.

  26. Alennx says:

    I have many thousands of cells in a Name column across 7 sheets, that have multiple ways of laying out a name:
    Johnny Doe
    Doe, Johnny
    John L Doe
    I can search them all by "Doe" but then how do I replace the entire contents of the cell with the correct data "John L Doe, Esq" instead of creating "Johnny John L Doe, Esq" ?

    Thanks in advance :)

  27. April Cavins says:

    I am trying to update 15 like workbooks with the same formula, but each workbook has its own name.

    The McKenzie workbook contains the correct formulas. Is there an easy way to copy the McKenzie spreadsheet to the Burkel spreadsheet pulling the data from the respective Formating spreadsheet?

    Hope I explained it well enough. Thanks so much!

  28. Andreas says:

    Great article!
    I am looking for a way to replace (remove) text from a column.
    In the column, there are names separated by a comma. I would like to Find and Replace (remove) everything that starts with "Fam" followed with two separate words, then comes a comma and the rest of the groups.
    It could look like this:
    Row 1 "Group1, Group2, Fam Adam Smith, Group 3, Group 4"
    Row 2 "Fam Angelina Jolie, Group 2, Group 6"
    Row 3 "Group 1, Group 5, Group 7, Fam Jessica Parker"
    It would like the end result with the above example to be like this:
    Row 1 "Group 1, Group 2, Group 3, Group 4"
    Row 2 "Group 2, Group 6"
    Row 3 "Group 1, Group 5, Group 7"

    Is it possible to achieve this with Find and Replace?
    Thanks a lot in advance!

  29. Kent says:

    =(SUMIF($M$18:$M$9997,"w/ Contractor",$O$18:$O$9997))/1000000

    I'm sure the answer has already been mentioned, however in the formula above, what's the shortcut for changing the letter => O in only ($O$18:$O$9997) to the letter N? I'd like to do a find and replace however the O in Contractor will also change.

  30. Fatma says:

    I am working with an excel sheet that contains calculated concentrations. I went myself through the data and highlight the numbers I want to exclude since they are so small (out of the range I am using). Is there a way to replace the values in these highlighted cells with a text (like ND or not detected)? It is a big sheet and I hope there is a way to do this step quicker than doing it manually.

  31. seidukeita says:

    how will i find and replace multiple of data in excel sheet,examples if i have a country names, january up to december but with the january there is a data in the january's. how will i find the rest of the data.

  32. DENIS says:


    How do i replace something if i have have @ in the cell


    @TZ_5 and i want to change it to @TZ_8 with find/replace


  33. JDR says:

    I am searching for a formula wherein I can replace a certain content of a cell with another cell's value.
    For Eg:
    A2 contains 12345
    B2 Contains AAAAA
    and C2 contains a text comment with "this is a XXXXXXX and due to this reason it is YYYYYY" I want to replace XXXXXXX with value of A2 and YYYYYYY with value of B2
    Thanks in advance!

    • HelpfulHelper says:

      Your formula would be
      ="this is a"&A2&" and due to this reason it is "&B2
      A year later but hopefully helpful.

  34. Ferbereyes says:

    Hi. Is there a way to use Find & Replace w/ new formatting but only reformat specific phrase not reformat the entire cell?

    For example, I want to make the phrase "Client Management" bold throughout the worksheet. Sometimes this phrase appears by itself in a cell, but in other cells, it may appear with other words (for instance, "Client Management Task Lists"). I only want the words "Client Management" to now appear bold. I tried the instructions listed above we/ options & formatting but the entire cell gets bolded vs. just the specific phrase. Thanks for your help!

  35. BALA says:

    kindly note that i have one issue. Here i'll explain with example

    "Corporation Co"

    I have to replace the word "Co" with "Company". But while using the Find and replace, the Corporation is also changed to "Companyrporation".

    Any solution to resolve this.


  36. Vianne says:


    Kindly do assist. Much appreciated in advance.

    My requirement:
    a) 1 shot Search and Replace multiple texts in specific worksheets of multiple workbooks.
    Example case:

    User has:
    -Excel workbook File1 with worksheet ‘ORI’, ‘R1’, ‘R2’, ‘R3’
    -Excel workbook File2 with worksheet ‘ORI’, ‘R1’, ‘R2’, ‘R3’, ‘R4’, ‘R5’
    -Excel workbook File3 with worksheet ‘ORI’, ‘R1’
    -Excel workbook File4 with worksheet ‘ORI’

    ORI = original
    R1 = Reissue 1
    R2 = Reissue 2
    R3 = Reissue 3
    R4 = Reissue 4
    R5 = Reissue 5..and so on

    In all worksheets mentioned above, exists text A1A, B1B, C1C, D1D DD
    User will like to do replacement as below:
    A1A : AA1AA
    B1B : BB1BB
    C1C : CC1CC
    D1D DD : DD1DD

    User only wants the replacement applies for: worksheet R3 of File1
    worksheet R5 of File2
    worksheet R1 of File3
    worksheet ORI of File4

    By default, system always selected on the latest active worksheets, for this case:
    worksheet R3 of File1
    worksheet R5 of File2
    worksheet R1 of File3
    worksheet ORI of File4

    Can this requirement be done?

    Kindly do advice.

    Thanks and Regards,

  37. Nejem says:

    When you search for a word in Excel, how do you instruct Excel to automatically scroll the row (that contains the found word) to the top of the spreadsheet ?

  38. Vesa says:

    Is there a way to use replace tool (CTRL'H) for an area with multiple columns and multiple rows to change e.g. AA1 AC4 AX7 to forms $AA$1 $AC$4 $AX$7 (To lock all the cells arfter filling them with pulling from corner) with only one hit of replace.

    To ask it other way is there a way to make excel ignore that second letter (A, C and X in our example) so that those would not change but i got the $ signs where I want them.

    And I want to achieve this without macros.
    Thank you so much.

  39. tony says:

    need to sort a massive spreadsheet of po numbers

    say its

    how can I eliminate the alphabet and keep the numbers using find and replace

    • Doug says:

      If the structure in every cell is one alpha and one number where the data is in A2 then the formula is: =RIGHT(A2,1)

  40. mikel says:

    how can i replace to many numbers/words at the same time at the same function?

    for example i want to replace 3210 to 3213 and 3208 to 3207 at the same time .

    • Doug says:

      Are the numbers you want to replace in the same row or different rows?
      Do the new numbers need to be in different rows?

  41. Simon says:

    So for this to work with the find feature (I could be looking for anything in any cell), I would have to select the entire worksheet prior to doing the Conditional Formatting, correct?

    • Doug says:

      Every cell you want to respond to this rule will have to have the Conditional Formatting applied. That does not mean you need to select every cell. You can enter the cell's range in the formula like this: A2:O278 in the applies to field.

  42. Simon says:

    How can I set up a spreadsheet so when the "Find" function is used, the resulting highlighted cell is more noticeable? Instead of a slightly thicker black border around the "target" or "found" cell, a red border, or the whole cell highlighted in yellow, et. al.....??????

    Thanks in Advance,
    Simon from Cincinnati

    • Doug says:

      Use conditional formatting to fill the cell or cells with your choice of background, etc. like this:
      Select the cell(s) you want to format
      Select the Conditional Formatting tab on your ribbon or wherever it may be in your version
      Choose New Rule
      Choose use formula
      Enter ="Find" in the field
      Choose the formatting option you want to use
      Choose OK

    • Michael Bednarczyk says:

      Select all cells by clicking the top left field. Then, when you use Find and Replace everything that is selected will be more visible.

  43. Shabroz says:

    I want to change cell number to cell containing value i.e.
    A1 = 1
    B1 = 2
    so in column C1, sum will be A1+B1
    I want cell containing values instead of cell reference numbers.

    Please guide me..

  44. Manideep says:

    How do i do
    Find N replace the value with -6.
    So if there is 7N then it should get -76. Can we do this in Excel?

  45. robert says:

    how do I do the sum of a not conforming fields. col.1 - col.4 = col.5
    example. col.1 col.2 col.3 col.4 col.5
    1234 23 45
    23 43 1256 answer
    1278 1 6
    5 67
    34 45 1356 answer

  46. maquignon says:

    How can I make the "Columns" option in Find and Replace STAY on Columns? It keeps reverting back to "Rows" and I always search by columns and have to change it almost every time I search.

  47. Judy Kunz says:

    In my Excel 2016, it is not allowing me to replace because the only Option it allows you to look in for replace is "formulas." Therefore, it cannot replace any value or comment. Is there something wrong with my edition or an option under "Find" I have not activated?

  48. Martin says:

    Hi can you help with replace :
    I have +427123456789 and i need to replace it to +421123456789 so in the replace i used in find field: +421********* and if i use in replace field +420 the rest disappear. Stars here not working... Can anybody know how to do it?

  49. Silvia says:


    It is any possibility to replace a space with nothing? Actualy I want to delete it.

    In some cells I have space between words. I want to find it and replace it with nothing.

    Thank you,

  50. DFL502 says:

    I need to change the first character of a range of cells that is = to :, and replace it with 0: and leave the rest of the filed as is. Not for sure how to do it!

  51. Farri says:

    How to replace multiple values with a single value at once?? For example, if I wanna replace Jacky, Jackson, Jimmy Farri etc with TOM, What procedure should I follow??

  52. Pandian says:


    Is there a way to replace all cells in a worksheet with value greater than X, with X while leaving other cells unchanged ?

    Ex: If there are 4 cells with value 50, and 8 cells with value greater than 1000, I want to change only those 8 cells (greater than 1000) to a value of 1000 and leave the 4 cells with value of 50 as it is. I did a conditional format to highlight those 8 cells but find & replace does not recognize the highlight from the conditional formatting. Any suggestions ?

  53. Sanjivkumar Desai says:

    This chapter contains lots of details regarding the topics I have to read it couple of time but if you can remember short cut I think it will be easy to do

      • Chuy Jimenez says:

        Hi Svetlana, in my excel when I press Ctrl+H does not opens on the Replace tab, do you have a solution for that? Do you know where I can change this?
        Thank you!

  54. Phelim says:

    I tried using CTRL J to replace line break in Excel 2016. It does not work. I get the standard error message "we couldn't find what you were looking for.

  55. S.H says:

    Hi there,

    Do you know how I can make Find highlight the found cell better than with a thin green line around it, I can't see that green line in my sheet. I am making a lot of quick searches in my document, so changing the color of the found cell manually is not an option.

  56. Anil says:

    how do i replace the following with just the numbers in number format

    00.00 1597532.28
    00.00 206860.65
    00.00 1342590.70
    1402471.36 00.00
    35497.63 00.00

    There is blank space appearing before each of these numbers and I cant replace it with CTRL F and R

    If you wish i can attatch a file also

  57. Chantal says:

    Good day

    I am working on a large database where I need to detect a single letter p or P or PP and then replace it with pp.

    But when I search p, all the pp's are also found.

    Is there a way to specify a specific letter or letters including case sensitivity?

  58. EJM says:

    I had trouble with Find & Replace (Excel 2003) but used this macro after playing around with macros:
    In Column A enter numbers from 1 to 5 in cells A1 through A5.
    In Column D use the corresponding designation to refer to Column A ( A1 is 1 so D1 would be =A1, etc.)

    A B C D

    1 = A1

    2 = A2

    3 = A3

    4 = A4

    5 = A5

    I was using Excel 2003
    Record a new Macro (In the Tools or Macro menu) click D1 on the worksheet and you should see " = A1 " in the Formula bar.
    Between the = sign and the letter A, input the dollar sign ($) =$A1 and then Stop Recording the Macro.

    Better yet! Just copy the following into your macro. This lists a column of 10 but you could have any number. EJM

    Sub Macro_List_of_Ten()
    For x = 1 To 10
    Cells(x, 4).Select
    ActiveCell.FormulaR1C1 = "=RC1"
    Next x
    End Sub

  59. Andrew says:

    can you find a value (part number) across many tabs (worksheets) and replace its related cost value that is in another cell on the same row with the updated cost amount?

    ColumnB ColumnF

    Find unit cost Replace
    AB-101 $15.00 $16.25 replace the $15.00 with $16.25

    Let me know

  60. Bree says:

    I've found over 40,000 occurrences of "". When I choose replace all I get an error that says invalid formula. I know this is wrong because I can replace them one by one without error. Is it just too many to replace all?

  61. Amber says:

    Hi there,

    I'm looking for some help on the 'Find and Replace' function in Excel 2016.

    I recently upgraded to 2016 and since then, my 'Find and Replace' function is appearing over the top of other programs and in its only separate window.

    How do I fix it to only appear when in Excel as this causes issues when I'm using the 'Alt+Tab' function to switch between programs.

    Thank you!

  62. Glenn says:

    Hey there,
    I have mountains of data that I need to go through and extract an alpha-numeric code from the text description from the same column, I'd like to be able to use find/replace to just remove anything with numbers, but it's not that easy, the description also has the address at the end and I can't allow that to be removed. Here is an example:-

    RD06024 Shed Storage - Redcliff - 145 Oxley Avenue Redcliff.

    I only want to remove the bar code at the beginning but if I use find/replace for numbers and letters it always takes out the address number as well.
    Can anyone please suggest something for me to try?
    Kind Regards

  63. DE WEt says:

    Want to replace number 0 .(Only the First 0 with 27)
    In a number.

    Please Help.
    Don't want to replace all the 0 with 27 only the beginning

  64. Nicola Grant says:


    I am having trouble with the replace function on excel. I need to do the following with a large database of data:

    Change all of 1s to 5s ***
    Change all of 2s to 4s ***
    Change all of 3s to 3s
    Change all of 4s to 2s ***
    Change all of 5s to 1s ***

    However, when I do this to each column it replaces all of them so It changes all the 5s again to 1 and all the 4s to 2 so my data is no longer accurate.
    Is there any way I can do this more effectively?


    • Willian Oliveira says:

      In situations like this, I would replace the 1s to 6s and 2s to 7s, this form I would have five differents values. After this, I would replace 5s to 1s, 4s to 2s, 6s to 4s and 7s to 5s.
      In some minutes, all is replaced...

  65. mai says:

    Can I automatically add different values all at once in one column against specific value in another column?
    for example: when ever its says 4 in one column the next cell in the next column should says 4288?

  66. BB says:

    How can I Replace "? thru ??" with "?-??" without the numbers changing to a date? I can set the format to text and it still changes it to a date which I do not want. I am only allowed to manually change it at this point, because anything I do otherwise changes the range of numbers to a date and if I change that date into a text, I get a weird number that doesn't have anything to do with the range I was changing in the first place.

    • Phil Hinton says:

      Yeah - tricky! I tried it with cell formatting and all, with no joy. If you can live with an 'em dash' (Insert Symbol or Alt+0151) rather than an hyphen:
      In 'Find what' put " thru " (i.e. by itself with a leading and trailing space)
      In 'Replace with' put an "em dash"

      1 thru 16
      1 thru 8
      2 thru 10
      5 thru 17

      you'll get:

      rather than:

    • Phil Hinton says:

      The other way, of course, is to use a formula like =SUBSTITUTE(A1," thru ","-"):
      Find " thru " Em dash Hyphen Formula
      1 thru 16 1—16 42370 1-16
      1 thru 8 1—8 43313 1-8
      2 thru 10 2—10 43375 2-10
      5 thru 17 5—17 42856 5-17

      • Phil Hinton says:

        Find " thru ".... — .... - ..... Formula
        1 thru 16 ...... 1—16 .. 42370 . 1-16
        1 thru 8 ...... 1—8 .. 43313 . 1-8
        2 thru 10 ...... 2—10 .. 43375 . 2-10
        5 thru 17 ...... 5—17 .. 42856 . 5-17

  67. John Riley says:

    Is there an option to replace at the same time more than one string only by using Find & Replace.
    Example:I have a column with websites addresses and I want to remove at the same time the ones that end with .com,plus these that end up with .net and the others with .org?
    Thanks in advance:
    Jonathan Riley

  68. Marvin says:

    Is there an easy way to search for two keywords at a time using the find and replace search option? For instance I'd like to search for "American Association for Justice" in a sheet that has a lot of entries for each of those individual words ("American", "Association", Justice"). In this case, I'd like to do a search using "American" and "Justice" together and find a result that has both of those words in the same cel. Is there a way to do that without creating a formula? I know I can search using wild cards like *American Association* and that works only if the two words are right next to each other in the cel. How do I search if both terms are not right next to each other? Is there a way to combine terms with an "&" or a"+" or something like that. This is especially important if the two terms are not right next to each other. I can do this in Outlook using the "+" symbol and it works great. How can I do this in Excel? Thanks

    • Phil Hinton says:

      Assuming that "American" is always before "Justice",you can simply use "American*Justice" in the 'Find what' field and click 'Find all'.
      This would find the 1st, 4th, 5th, and 6th term in the list below:

      American Association for Justice
      Justice League of America
      Association for Justice
      American Justice
      American Association for Justice
      American Justice Association
      American Association

      • Nick says:

        Hey Phil

        I am having the same problem when attempting to CTRL F in a spreadsheet.
        The spreadsheet I'm using has way too many of each option to just search with one word.
        My words are not always in the same sequence but so your option will only work sometimes, is there any other possible ways to search for 2 words?


  69. Dave says:

    Is there a way to change the color green used to identify the searched item to another color. My eyes have a difficult time seeing the green.

  70. Sean says:

    Hi there! I would love a shortcut to get rid of things that don't necessarily match but have similar characteristics. For example a field that says:
    Words Words 00000001 To John Smith
    Words Words 01010101 To Jane Smith
    Words Words 02020202 To Jim Smith

    I would like to eliminate From Words (always matching) - To (always matching including the space). The end result would be:

    John Smith
    Jane Smith
    Jim Smith

    Thanks much!

    • Phil Hinton says:

      It's 18 months later, but to answer your question for posterity:
      In 'Replace > Find what' put "Words Words * To " (without quote marks)
      In 'Replace > Replace with' leave the field blank
      Click 'Replace All'

      • Phil Hinton says:

        Tch! I shouldn't have copypasted your text string! I meant:

        In 'Replace > Find what' put "w*o " (with a space after the o but without quote marks).

        Still, the earlier version also works.

  71. Val says:

    I am entering data in a large spreadsheet. The column I am entering in is not visible at first - you have to scroll over, because it's several columns over. When I enter my values, and then SEARCH, the word is found, but it moves the cursor back to the beginning of the spreadsheet, even though the column I'm searching in is one that is visible when my data entry column is. It didn't do this yesterday, before my computer auto-updated.

  72. Steven says:

    Hi -
    Is there a way to save frequently used Find & Replace executions so I don't have to keep inputting the data each time?

    Thank you.
    Steven P

  73. Chin Hean says:

    I encounter one issue and would like to ask if there is any solution for this, if not, would like to suggest for the developer to come out with a solution for this.

    E.g. I had key in the NRIC of a person, "S1234567A". Due to privacy problem, I would like to hide the first 4 figure of the NRIC to "SXXXX567A". I have more than hundreds of data in the database and couldn't afford to change it one by one manually.

    Thus, can I know is there any shortcut or fast way to change each and everyone of it immediately? Something just like the "Find and Replace" function of Microsoft Excel.

    Thank you.

  74. Linda Haley says:

    I have used find and replace for replacing item names, employees, and many other changes or correctionto nformation within the cells.


    Please how can i analyse a qualitative data on the operational asessment of health facilities on excel

  76. Mike says:

    I want to find and replace a particular set of numbers and letters, lets say A12B with a number, lets say 40. When I try this excel finds all cells that contain the string including additional letters and or numbers. so it might find D2a12brr. Is there any way of finding only A12B.


  77. Carolyn says:

    Is there a way to save my Find and Replace criteria? I regularly have to apply the same changes to similar datasheets; it would save me a lot of time if I could open a datasheet and then, with a few clicks, apply a set of replacements, as opposed to having to enter the Find criteria and Replace criteria each time. Thanks!

  78. Eileen says:

    Is there a way to search for a cell reference and replace with another cell reference and using a wild card? Let me explain...

    I have probably 20 cells on a worksheet that I need to change the cell reference from Rxx (where xx is the cell number) to Txx. So Column R, cell xx to Column T, cell xx. The cell number is different across the 20 cells and that part I want to preserve.

    I have to do this for 26 worksheets, about 20 cells per worksheet.

  79. Mattice says:

    How do I use wildcards in the replace section of find and replace?

    I want to find "=??29" then replace that with "=??10" where the first question mark in the replace statement is the same character as the first question mark in the find statement.
    for example, in just one search I want "=AG29" to go to "=AG10" and "=BC29" to go to "=BC10"

  80. Jeremiah Mlambo says:

    Is there a way (or add-in)that can replace multiple values at once ,for example:

    original value replacing value
    tom thomas
    jerry jeremiah
    xmas christmas
    soon later
    1234 56789

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)