Elettracompany.com

Компьютерный справочник
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Find and replace excel

Find or replace text and numbers on a worksheet

In this course:

Use the Find and Replace features in Excel to search for something in your workbook, such as a particular number or text string. You can either locate the search item for reference, or you can replace it with something else. You can include wildcard characters such as question marks, tildes, and asterisks, or numbers in your search terms. You can search by rows and columns, search within comments or values, and search within worksheets or entire workbooks.

To find something, press Ctrl+F, or go to Home > Editing > Find & Select > Find.

Note: In the following example, we’ve clicked the Options >> button to show the entire Find dialog. By default, it will display with Options hidden.

In the Find what: box, type the text or numbers you want to find, or click the arrow in the Find what: box, and then select a recent search item from the list.

Tips: You can use wildcard characters — question mark (?), asterisk (*), tilde (

) — in your search criteria.

Use the question mark (?) to find any single character — for example, s?t finds «sat» and «set».

Use the asterisk (*) to find any number of characters — for example, s*d finds «sad» and «started».

) followed by ?, *, or

to find question marks, asterisks, or other tilde characters — for example, fy91

Click Find All or Find Next to run your search.

Tip: When you click Find All, every occurrence of the criteria that you are searching for will be listed, and clicking a specific occurrence in the list will select its cell. You can sort the results of a Find All search by clicking a column heading.

Click Options>> to further define your search if needed:

Within: To search for data in a worksheet or in an entire workbook, select Sheet or Workbook.

Search: You can choose to search either By Rows (default), or By Columns.

Look in: To search for data with specific details, in the box, click Formulas, Values, Notes, or Comments.

Note: Formulas, Values, Notes and Comments are only available on the Find tab; only Formulas are available on the Replace tab.

Match case — Check this if you want to search for case-sensitive data.

Match entire cell contents — Check this if you want to search for cells that contain just the characters that you typed in the Find what: box.

If you want to search for text or numbers with specific formatting, click Format, and then make your selections in the Find Format dialog box.

Tip: If you want to find cells that just match a specific format, you can delete any criteria in the Find what box, and then select a specific cell format as an example. Click the arrow next to Format, click Choose Format From Cell, and then click the cell that has the formatting that you want to search for.

Replace

To replace text or numbers, press Ctrl+H, or go to Home > Editing > Find & Select > Replace.

Note: In the following example, we’ve clicked the Options >> button to show the entire Find dialog. By default, it will display with Options hidden.

In the Find what: box, type the text or numbers you want to find, or click the arrow in the Find what: box, and then select a recent search item from the list.

Tips: You can use wildcard characters — question mark (?), asterisk (*), tilde (

) — in your search criteria.

Use the question mark (?) to find any single character — for example, s?t finds «sat» and «set».

Use the asterisk (*) to find any number of characters — for example, s*d finds «sad» and «started».

) followed by ?, *, or

to find question marks, asterisks, or other tilde characters — for example, fy91

In the Replace with: box, enter the text or numbers you want to use to replace the search text.

Click Replace All or Replace.

Tip: When you click Replace All, every occurrence of the criteria that you are searching for will be replaced, while Replace will update one occurrence at a time.

Click Options>> to further define your search if needed:

Within: To search for data in a worksheet or in an entire workbook, select Sheet or Workbook.

Search: You can choose to search either By Rows (default), or By Columns.

Look in: To search for data with specific details, in the box, click Formulas, Values, Notes, or Comments.

Note: Formulas, Values, Notes and Comments are only available on the Find tab; only Formulas are available on the Replace tab.

Match case — Check this if you want to search for case-sensitive data.

Match entire cell contents — Check this if you want to search for cells that contain just the characters that you typed in the Find what: box.

If you want to search for text or numbers with specific formatting, click Format, and then make your selections in the Find Format dialog box.

Tip: If you want to find cells that just match a specific format, you can delete any criteria in the Find what box, and then select a specific cell format as an example. Click the arrow next to Format, click Choose Format From Cell, and then click the cell that has the formatting that you want to search for.

There are two distinct methods for finding or replacing text or numbers on the Mac. The first is to use the Find & Replace dialog. The second is to use the Search bar in the ribbon.

Читать еще:  1с active document excel

How to use Advanced Options in Find & Replace in Excel

Everyone know that we can find and replace a text with find and replace option. But very few people know that you can use this option to an advance level.
Do you?
If not, then after reading this post you’ll love this tool. In this post, you will learn some useful examples to use find and replace option to an advance level.
So let’s get started.

First of all, you have to activate advanced options in find and replace. Click on the “Options” button to activate.

With advanced options, you can find values using different parameters. You can specify conditions like searching for specific formulas, cells with specific colors, bold texts, etc.

Here are some useful examples for searching some specific values:

  1. Value In Entire Workbook
  2. Cell Having a Specific Formula
  3. Values from Cell Comments
  4. Copy Format From A Cell To Find Another Cell
  5. Having Specific Format

Find Value In Entire Workbook

You can find a value from your entire workbook rather than from a single worksheet.
And, you can also replace that value with some other specific value.

  • Step-1: To find a value in an entire workbook, select workbook from “Within” drop down option.
  • Step-2: Enter search value in “Find what” to search & enter replace value in “Replace with” to replace.

Find Cell Having a Specific Formula

This is also a useful option to find a cell which has a specific formula and replace that formula with some other specific formula.

  • Step-1: To find a cell with a specific formula, activate your find and replace option. And, select formula option from “Look in” drop down.
  • Step-2: Now, enter the formula in “Find what” to search for formula and enter the formula in “replace with” you want to replace.

Find Specific Value From Cell Comment

This one is my favorite. You can find the cell in which you have a cell comment with a specific value. This will help you in two ways, first, it will search for the cells which have the cell comment and then it will search a specific value in that comment.

  • Step-1: To do this, activate your find and replace option. Select “Comments” from “Look In” dropdown.
  • Step-2: Add the value in “Find What” you want to search for.
  • Step-3: Click find or find all.

Copy Format From A Cell To Find Another Cell

Here we have another useful option to find a cell on the basis of value and format of a specific cell.
And, after that, you can also change that value and format.

  • Step-1: Click on the down arrow on the format button.
  • Step-2: Select “Choose Format From Cell”. You will get a selection tool to select the cell which you want to use as a base for your search.

You can also select a cell to take as a base for replacement.

Find Cell Having Specific Format

You can also use the custom format to search and replace cell values and formats.

  • Step-1: Activate your find and replace option, Click on the format button.

  • Step-2: You’ll get a format dialog box to select the format to find for. Select the desired format to search for.

Using this option you will able to make any type of search basis on the format.
You can search for:

Things to Remember

  1. There is no option to replace the value in cell comment.
  2. While searching for a format, you can only search for the cells which exactly have the same format.
  3. And for text, you have the option to match case.
  4. You can also match exact cell content for your search.

Conclusion

Using find and replace this way can save your lot of time. This can give you an advanced way to search for the values. And the best part is you, you can replace those values.
I hope you like it. Do you have any unique idea to use this option? Please share your view in the comment box, I would love to hear from you.

Using Excel Find and Replace tools

What are these tools good for?

Looking for a name inside a list of 20 names is easy – you can simply look it up with your eyes. But looking for it inside a list of 400 names – well, it starts to be a problem. And what about a bank manager looking for the account number 9374857 among all of his branch accounts?

The “Find” tool in Excel enables you to type a query, and let Excel find it for you. If a match is found – the active cell will “jump” right into it.

The “Replace” tool allows you to make a large systematic change in a matter of seconds. Imagine the telephone area code changed from 072 to 074. If you have a database with telephone numbers, you can use this tool to systematically change all these numbers.

Another example:
Consider you have a shift schedule set for your business and “William” is placed in for shifts in the following month. Suddenly William has resigned and Claire came instead.
Using the “Replace” tool you can immediately replace all “William” by “Claire” in your spreadsheet (or select a certain portion of the spreadsheet, and replace only within the selected area).

You can feel free to experiment with this tool, since the “Undo” button is always available immediately afterwards.

How to use the «Find» tool

Step by step guidance:
1. Click the binoculars button, and choose “Find”.
Alternative: press together [Ctrl]+[F] keys on the keyboard.
The “Find and replace” dialog box will appear, in which the “Find” tab is selected:

Читать еще:  Узнать номер строки excel vba

2. Type down the query you wish to look for.

3. Click the “Find next” button, or press [Enter] on the keyboard.
The active cell will jump immediately to the first match found.

4. You can now close the dialog box by either clicking the “Close” button or by pressing “Esc” on the keyboard, or you can look for more matches by clicking the “Find next” button again.

In case Excel doesn’t find any match, the following message will appear:

Limiting the search area

How does Excel search your worksheet?

It starts from the location of the active cell, and goes to the right along the row. After finishing the row, its goes one row down and searches on the next row from left to right, and so on until the last row of you data. Then, it jumps back to the first row of your data, until reaching the active cell again.

That gives high importance to the location (especially the row) of the active cell when conducting a search.
For example, if you have the word “smith” many times along your worksheet, and you start a search when the active cell is in address C8, the computer will find the match located in address K46 before it will find the one located in address E5. (This is because the search started at row 8.)

Excel Tip: ‘Find and Replace’ Problems

Why can’t Excel find a number?
Bill Jelen

User question: The Excel Find and Replace dialog drives me crazy. I always have to go to the Options button to specify that it should look in Values. In the figure below, the mouse pointer is showing the value that Excel says is not actually there. Why can’t Excel find a number?

Mr. Excel: Psst, Excel! Try looking under the mouse pointer. You’ve pointed out a lot of the problems with Find and Replace [in Excel]. Let’s take a quick review to uncover some of them. First, when you select Home, Find & Select, Find, Excel presents the simplified version of the Find and Replace dialog without the important settings shown at the bottom of the figure (left).

There are important settings hiding behind the Options button. These settings often cause a Find to fail. Say that you have a calculation for sales tax in column D. Cell D3 shows 70.81 as the result of a formula. By default, Excel searches the formulas instead of the values. If you tried Find without changing Formulas to Values, it will not find $70.81.

Searching the text of the formulas is a bit annoying. How often do you say to yourself,
“Wow, I wonder in which cell I used the SQRTPI function?” But even more annoying
are the other settings, such as Match Case and Match Entire Cell Contents. These settings can be useful, but if you happened to change them at 8:04 a.m. today and haven’t closed Excel since then, even though you’ve opened and closed 40 other workbooks and are working on something completely different, Excel will remember that previous setting. You will often get stung by a strange setting left behind earlier in the day, or even a setting changed when a macro tried to use the Find command with Match Entire Cell Contents turned on.

So why can’t Excel see the 1354.80 value in the figure? Excel is displaying cell C16
with a currency symbol and a comma, and in order to find the cell, you have to search
for $1,354.80! Because Excel’s forté is numbers, it’s rather disappointing that Excel
works like this. But when you understand it, you can work around it.

ADDITIONAL DETAILS: People often ask about how it is possible to search through all sheets in a workbook. You do this by changing the Within dropdown from Sheet to Workbook.

ADDITIONAL DETAILS: Amazingly, Excel can find cells that are displaying as number
signs (#) instead of numbers. Say that you have a column where 5% of the numbers are
showing as #####.

Now, any sane person would make the column wider or turn on Shrink to Fit, but
Excel allows you to perform the following rather crazy set of steps:

1). Select the range of numbers. Press Ctrl+F to display the Find dialog.

2). Type ### in the Find What dialog.

3). If the dialog is not showing the options, click the Options button.

4). Ensure Look In is set to Values and Match Entire Cell Contents is not checked.

5). Instead of clicking Find, click Find All. Excel adds a new section to the dialog, with a list of all the cells that contain ###.

6). While still focusing on the dialog, click Ctrl+A. This will select all the cells in the bottom of the Find All dialog.

You can now format just the selected cells. For example, you could choose
fewer decimals, or a smaller font size, or you could even choose to display the numbers
in thousands.

CAUTION: In Step 6, you are supposed to press Ctrl+A to select all of the found cells.
Be careful that the focus is on the dialog box before pressing Ctrl+A. For example, if
you change the font size, the focus would switch to the worksheet, even though the dialog is still displayed. Pressing Ctrl+A at this point would select all cells in the worksheet instead of just the matching cells. To reestablish focus on the dialog box, you need to click the title bar of the Find and Replace dialog.

This article first appeared in the December 15, 2014, edition of the CFO LearningPro, Excel Edition, newsletter.

How to Find and Replace in Excel:
Wildcards, Values, and Formulas!

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel’s search functions are great . . . but if you aren’t using the result in another cell, you can use a more familiar tool.

Читать еще:  Вставить вкладку в excel

The Find and Replace dialog is very powerful, especially once you get a handle on its more advanced features.

In this tutorial, we’ll show you how to find and replace data in your spreadsheet and share some tips on how to make your search more efficient.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

Table of Contents

The Find and Replace dialog

To start, we’ll take a quick look at the Find and Replace dialog itself.

Open the dialog by pressing Ctrl + F. You can also click the Find & Select button on the far right side of the Home tab and choose Find… or Replace…

This will bring up the basic Find & Replace dialog.

As you can see, this dialog doesn’t offer a whole lot of options (yet). But don’t let that fool you—it can be an extremely useful tool.

Clicking the Replace tab brings adds another field, and enables the find-and-replace functionality.

And clicking the Options >> button in either tab brings up a slew of extras:

Get your FREE exercise file

Learning to find and replace data is easier when you have a spreadsheet to work on.

Grab our free exercise workbook to follow along with the rest of the article!

Download the FREE Exercise File

How to search in Excel

The search function of the Find and Replace dialog is just like you’d expect. Open the dialog, type in your search query, and hit Enter, click Find Next, or hit Find All to run your search.

Let’s open up the example workbook and run a search to see how it works. We’ll look for a specific car manufacturer, Subaru.

Hit Ctrl + F to open the Find and Replace dialog, and type “subaru” into the search field:

Hit Enter or click Find Next, and Excel will scroll to the first result and highlight it for you:

Notice that the capitalization doesn’t matter in this case.

Now, click Find All to see how the results are displayed.

Excel displays a list of all the occurrences of your search query in the document. Click on any of the results in the list to go to that instance.

You can use this method to search for text or numbers.

Search wildcards

Wildcards are useful in formulas, but you can also use them to make your find-and-replace actions easier, too.

Use the asterisk when you’ll accept a string of any length.

For example, a search for “g*i” will return “Gething,” “Lamborghini,” and “GTI.”

The question mark will only return values that have a single character in that space. A search for “g?i” won’t return “Gething” or “Lamborghini,” but will return “GTI.”

If you ever want to search for a string with a wildcard character in it, you can tell Excel not to treat an asterisk or a question mark as a wildcard by prefacing it with a tilde.

So “g*i” has a wildcard, but “g

*i” doesn’t. The second search will only return cells that match the string “g*i.”

Advanced search options

Now that you have basic searching down, let’s take a look at some of the more advanced options you have for finding data.

Click the Options >> button in the Find and Replace dialog. You’ll see a wide variety of new choices. Here’s what they do:

  • Format… lets you narrow your search to specific cell formats (we’ll see shortly how useful this can be).
  • Within: allows you to choose to search the entire workbook, instead of a single sheet.
  • Search: sets the search to run by row or by column, changing the order in which you’ll see your search results—this can be useful if you have a massive spreadsheet and want to the search to run left-to-right instead of up-to-down.
  • Look in: tells Excel where to look for your search query (we’ll talk about this in a moment).
  • Match case makes the search case-sensitive.
  • Match entire cell contents tells Excel to only return cells that match your search query exactly, and don’t contain anything else.

Several of these options are self-explanatory, but we’ll take a look at some of the more esoteric options.

First, let’s see how the Look in menu changes our search. Click over to the second sheet in the example workbook, where you’ll see names, vehicle makes and models, and the base price, discount, and sale price of each vehicle.

It’s important to notice that the sale price is determined by a formula (in this case, it’s the base price multiplied by 1 minus the discount). That will become important. Take a look at the first number in the sale price column:

Let’s run a standard search for the number “26,229” and see what happens.

Excel doesn’t find anything because the default search looks at formulas. Because “26,229” isn’t in the formula (which is =C2*(1-D1)), we don’t get a result. Let’s change the search scope to Values and run it again.

The search works perfectly now. When you search for values, you’re searching what’s displayed in the cell, and not the formula that creates it.

So if you have a spreadsheet with a lot of formulas, this is a good thing to remember.

The rest of the advanced search options, other than searching by format, are self-explanatory. If you’re not sure how one of them works, try it out and see!

Ссылка на основную публикацию
Adblock
detector