Comments on: 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 specified formatting, find and replace in all open workbooks and more. Continue reading →

Comments page 2. Total comments: 92

  1. need to sort a massive spreadsheet of po numbers

    say its
    a1
    b2
    g4
    c2
    g3

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

    1. Tony:
      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)

  2. hi
    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 .

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

  3. 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?

    1. Simon:
      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.

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

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

    2. Simon:
      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

  5. 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?
    M.

    1. Hello, Martin,

      Before replacing, try deleting all the asterisks (*) from the "Find what" field.

      Hope this will help you.

  6. Hi,

    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,
    Silvia

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

    1. Hi Sanjivkumar,

      You can use one of the following shortcuts to open Excel's Find and Replace dialog:

      Ctrl+F - opens the Find tab.

      Ctrl+H - opens the Replace tab.

      1. 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!

  8. Hi,

    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?

    Thanks
    NIC

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

  9. How do I replace the below strings by 11 and remove everything after it?

    11.234.45.6
    11.03.28.56

    1. find value 11.*
      replace value 11

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

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

      1. Tch!
        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

        1. I'm not liking these comment boxes!
          Well, you get the idea.

    2. 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"

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

      you'll get:
      1—16
      1—8
      2—10
      5—17

      rather than:
      42370
      43313
      43375
      42856

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

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

      1. 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?

        Thanks

    2. did you figure this out? I'm looking for the same solution

      1. See my reply to Marvin, Brian.

  12. 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!

    1. 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'

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

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

    1. Ctrl + S

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

    1. Hi Chin Hean!
      You could use the REPLACE Function in Excel:

      =REPLACE(A2,2,4,"XXXX") where

      'A2' - cell no where you key the NRIC in
      '2' - the position of the digit where you want to start replacing at (From Left to Right)
      '4' - the number of digits you would like to replace
      'XXXX' - what you would like to replace with

      More info at: https://www.ablebits.com/office-addins-blog/excel-replace-substitute-functions/

      Good Luck! :D

  15. 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"

    1. Hello Mattice,

      You can use the standard Find and Replace tool in Excel (Ctrl+H) to replace 29 with 10 in the selected range. If this solution may corrupt other records, then you can copy your data to a Word file and follow these steps:
      - Click Ctrl+H, enter the following entry in the Find what field:
      =(??)29
      - Enter the following line in the Replace with field:
      =\110
      - Click "More" and check off the option to "Use wildcards"
      - Click Replace All

      You can find a detailed description of using regular expressions in Word here:
      https://support.office.com/en-us/article/Find-and-replace-text-by-using-regular-expressions-Advanced-eeaa03b0-e9f3-4921-b1e8-85b0ad1c427f

      1. thats completely false, excel dont support braced references and moreover your link only show the very basic "search and replace" fonctionality, in no way it shows regular expressions :((((

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

      1. Can we do it with only some of the cell?

  17. How can convert a numeric value in one cell into English words in another cell in Excel

      1. Hi,
        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.

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)