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 4. Total comments: 169

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

  2. thank you very much, this is very useful for me :)

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

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

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

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

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

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

  9. Thank you for this tutorial!

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

  11. Dear,
    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??

  12. Hi,

    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 ?

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

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

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

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

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

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

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

  20. Hi,
    how i can find the value which is in drop down list in excel?

  21. Hello,
    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?

  22. 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!
    Amber

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

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

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

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

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

  27. Got it.

    Replaced 11.* by 11

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

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

  30. Hi,there
     
    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

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

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

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

  34. Why does find and replace clear the formated and bolded text in my cell when im just replacing a few words?

  35. Columns of data in format
    Jan 1 1980
    I want to replace the Space with a /
    Jan/1/1980

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

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

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

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

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

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

    Thanks

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

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

  44. Hi there.,

    How to change the format of cell value when I search or find (Ctrl+F) in excel?

  45. how do you find anything with a numeric value and replace it with nothing?

  46. 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 :((((

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

  48. forgive but yes there are a way to do this with formulas

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



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 :)