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

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

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

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

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =REPLACE(A1,SEARCH(";",A1,1),1,"")

      Hope this is what you need. If something is still unclear, please feel free to ask.

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

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

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

  5. I have a list of prices in a column for example:
    23,456
    24,678
    25,789
    ...
    How do I change the last 3 digits of each one to 151 so that in that column it now says:
    23,151
    24,151
    25,151
    ...

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

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

  8. 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. What and where should be added in the formula plz?

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

    2. Hello Abdul!
      If you enter text with your hands, you can select part of it and use the desired format. Using an Excel formula, you can format only the entire cell.

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

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

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

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

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

    1. 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: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial

  12. I want to replace
    01:01/162:01 which is text
    with
    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

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

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

  15. How to replace different cell in together or remove word from attached number?

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

  17. How to replace shapes in Excel to number value for example circle replace with no 3..
    Plz guide

  18. How to replace multiple different values(huge amount of data) to a single number?
    Thanks in advance

  19. 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
    etc...
    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 :)

  20. I am trying to update 15 like workbooks with the same formula, but each workbook has its own name.
    =SUMIF(Formating!$C$295:$F$306,McKenzie!$A95,Formating!$E$295:$E$306)
    =SUMIF(Formating!$C$292:$F$304,Burkel!$A95,Formating!$E$292:$E$304)

    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!

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

  22. =(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

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

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

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

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

  27. Hii..

    How to * And ? replace with Blank cell

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

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

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

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

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

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

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

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

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

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

  38. Got it - thanks!

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

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

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

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

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

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

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

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

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

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

  49. Thank you for this tutorial!

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

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