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

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

  2. =(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.
    Thanks,
    Kent

  3. Hey,
    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.
    Regards,
    Fatma

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

  5. HELLO!

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

    example

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

    regards
    DENIS

  6. Hello,
    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!

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

  7. Hii..

    How to * And ? replace with Blank cell

  8. Hi
    I want add .0 after number
    How can i do?

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

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

    Thanks

    1. Type "Corporation Co" in the FIND field, then type "Corporation Company" in the REPLACE field

  11. Hi

    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’

    Note:
    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,
    Vianne

  12. 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 ?
    Thanks

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

  14. How can I use FIND where it will search from the bottom up or from the most recently entry up?

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

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

  17. How do I amend the default "Find" to columns instead of rows

  18. Got it - thanks!

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

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

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

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

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

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

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

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

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

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

  29. Thank you for this tutorial!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  47. Got it.

    Replaced 11.* by 11

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

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

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

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