Find or replace text and numbers on a worksheet (2023)

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.

Find or replace text and numbers on a worksheet (2)

WindowsmacOSWeb

Find

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.

Find or replace text and numbers on a worksheet (3)

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

    • Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters — for example, fy91~? finds "fy91?".

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

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

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

(Video) Find or replace text and numbers on a worksheet

Find or replace text and numbers on a worksheet (4)

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

    • Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters — for example, fy91~? finds "fy91?".

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

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

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

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

Find & Replace dialog

Search bar and options

Find or replace text and numbers on a worksheet (5)

Find or replace text and numbers on a worksheet (6)

Find or replace text and numbers on a worksheet (7)

Find or replace text and numbers on a worksheet (8)

  1. Press Ctrl+For go to Home > Find & Select > Find.

  2. InFind what:type the text or numbers you want to find.

  3. SelectFind Next to run your search.

  4. You can further define your search:

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

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

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

  • Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters — for example, fy91~? finds "fy91?".

(Video) Find or replace text and numbers on a worksheet | Microsoft Excel Tutorial

Find or replace text and numbers on a worksheet (9)

  1. Press Ctrl+For go to Home > Find & Select > Find.

  2. InFind what:type the text or numbers you want to find.

  3. SelectFind All to run your search for all occurrences.

    Find or replace text and numbers on a worksheet (10)

    Note:The dialog box expands to show a list of all the cells that contain the search term, and the total number of cells in which it appears.

  4. Select any item in the listto highlight the corresponding cell in your worksheet.

    Note:You can edit the contents of the highlighted cell.

  1. Press Ctrl+Hor go to Home > Find & Select > Replace.

  2. InFind what, type the text or numbers you want to find.

  3. You can further define your search:

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

      (Video) How to find or replace text and numbers on a Excel worksheet | Microsoft

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

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

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

      • Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters— for example, fy91~? finds "fy91?".

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

  5. SelectReplace or Replace All.

    Tips:

    • When you selectReplace All, every occurrence of the criteria that you are searching for is replaced.

    • When you selectReplace, you can replace one instance at a time by selecting Next to highlight the next instance.

  1. Select any cell to search the entire sheet or select a specific range of cells to search.

  2. Press Command + F or select the magnifying glass to expand the Search bar and type the text or number you want to find in the search field.

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

    • Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters— for example, fy91~? finds "fy91?".

  3. Press return.

    Notes:

    • To find the next instance of the item you are searching for, press return again or use the Find dialog box and select Find Next.

    • To specify additional search options, select the magnifying glass and select Search in Sheet or Search in Workbook. You can also select the Advanced option, which launches the Find dialog.

    Tip:You can cancel a search in progress by pressing ESC.

Find

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

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

(Video) replace text and numbers on a worksheet | how to replace text and numbers on a worksheet

Find or replace text and numbers on a worksheet (11)

  1. In the Find what: box, type the text or numbers you want to find.

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

    • Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters — for example, fy91~? finds "fy91?".

  2. Click Find Next or Find All 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.

  3. Click > Search Options to further define your search if needed:

    • Within: To search for data within a certain selection, choose Selection. To search for data in a worksheet or in an entire workbook, select Sheet or Workbook.

    • Direction: You can choose to search either Down (default), or Up.

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

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 > Search Options to show the entire Find dialog. By default, it will display with Search Options hidden.

Find or replace text and numbers on a worksheet (12)

  1. In the Find what: box, type the text or numbers you want to find.

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

    • Use the tilde (~) followed by ?, *, or ~ to find question marks, asterisks, or other tilde characters — for example, fy91~? finds "fy91?".

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

  3. Click Replace or Replace All.

    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.

  4. Click > Search Options to further define your search if needed:

    • Within: To search for data within a certain selection, choose Selection. To search for data in a worksheet or in an entire workbook, select Sheet or Workbook.

    • Direction: You can choose to search either Down (default), or Up.

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

Need more help?

You can always ask an expert in the Excel Tech Communityor get support in the Answers community.

(Video) Excel - Find or replace text and numbers on a worksheet

FAQs

How do I find and replace text in Excel? ›

Replace Text
  1. Click the Find & Select button on the Home tab.
  2. Select Replace. Press Ctrl + H. ...
  3. Type the text you want to replace in the Find what box.
  4. Type the replacement text in the Replace with box.
  5. Click the Find Next button. The first instance is selected.
  6. Click any of the following: ...
  7. Click Close when you're finished.

How do I remove numbers from a cell that contain text and numbers in Excel? ›

Find/ Replace with Wildcards
  1. highlight the column,
  2. Click CTRL + H (to bring up the find replace tool)
  3. In the Find What box type (*)- this tells Excel if must look for a '(', then other characters (as many as there are), and then a ')'.
  4. The Replace with box is left blank.
  5. Then click Replace All.
22 Jun 2022

How do I find and replace in numbers? ›

Click View in the toolbar, then choose Show Find & Replace. in the Find & Replace window, then choose Find & Replace. You can also select other options, such as Whole Words and Match Case, to refine your search.

How do you find and replace multiple values at once in Excel? ›

The easiest way to find and replace multiple entries in Excel is by using the SUBSTITUTE function. The formula's logic is very simple: you write a few individual functions to replace an old value with a new one.

How do I find and replace text? ›

Try it!
  1. Select Replace or press Ctrl + H. ...
  2. In the Find what box, type the text you want to search for.
  3. Select Find Next to see where the text appears in your file. ...
  4. In the Replace with box, type the text you want.
  5. Select Replace to change the text or select Replace All to change all instances of this text in your file.

How do you do find and replace on sheets? ›

Use find and replace in a spreadsheet
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click Edit. ...
  3. Next to "Find," type the word you want to find, If you want to replace the word, enter the new word next to "Replace with."
  4. To search for the word, click Find. ...
  5. Optional: Narrow your search by using an option below.

How do I separate a string of text and numbers in Excel? ›

Follow these steps for separating numbers from text using Excel's "Flash Fill" tool:
  1. Type the number from your first string. Manually enter the numbers from your first string in the adjacent blank cell. ...
  2. Select the range for your numbers. ...
  3. Click "Flash Fill" under the "Data" tab. ...
  4. Repeat to separate text.
8 Apr 2022

How do I remove numbers from a cell in Excel without formula? ›

1. Select the text string cells that you will remove numbers from, and click Kutools > Text > Remove Characters. 2. In the opening Remove Characters dialog box, please check the Numeric option, and click the Ok button.

How do I remove a specific number of characters in a string in Excel? ›

How to remove specific character in Excel
  1. Select a range of cells where you want to remove a specific character.
  2. Press Ctrl + H to open the Find and Replace dialog.
  3. In the Find what box, type the character.
  4. Leave the Replace with box empty.
  5. Click Replace all.
16 Jun 2022

What is the Find and Replace option? ›

Find and Replace helps you to find words or formats in a document and can let you replace all instances of a word or format. This is particularly handy in long documents. To use Find and Replace, use the shortcut Ctrl+H or navigate to Editing in the Home tab of the ribbon, then choose Replace.

How do you find and replace in a formula? ›

Here are the steps to do this:
  1. Select the cells that have the formula in which you want to replace the reference. ...
  2. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H).
  3. In the Find and Replace dialogue box, use the following details: ...
  4. Click on Replace All.

What is the formula for replace? ›

=REPLACE(old_text, start_num, num_chars, new_text)

The REPLACE function uses the following arguments: Old_text (required argument) – This is the text we wish to replace some characters.

How do you find and replace multiple values at once in Excel macro? ›

Excel Multi Replace
  1. Open your VBA editor ( Alt + F11 ) and paste the below macro anywhere.
  2. Set up a two-column lookup range: 1st column is the value to search for, 2nd the value to replace.
  3. Select your input range where values should be replaced like shown in the 1st picture.
  4. Execute the macro ( Alt + F8 ).
16 Jul 2013

How do you get to Advanced Find and Replace? ›

Find text with specific formatting

In the Navigation Pane, select the magnifying glass. , and then select Advanced Find & Replace. On the Format menu, select the option that you want. If a second dialog box opens, select the options that you want, and then select OK.

How do you calculate the same on multiple sheets in Excel? ›

Click the tab for the first worksheet that you want to reference. Hold down the Shift key then click the tab for the last worksheet that you want to reference. Select the cell or range of cells that you want to reference. Complete the formula, and then press Enter.

What is the shortcut key for finding and replacing text in a document? ›

You can display the Find and Replace dialog box by pressing Ctrl + H. Alternatively, you can also use a key tip shortcut by pressing Alt > H > R (Alt then H then R). The Find and Replace dialog box appears as follows (with the Replace tab selected):

How do I find and replace blank cells in sheets? ›

To do so, click the Edit tab and then click Find and replace. In the new window that appears, type ^\s*$ to find blank cells and use 0 as the replacement. Type in the cell range you'd like to use this formula on, then check the boxes next to Match case and Search using regulars expressions.

How do you find and replace multiple values at once in Google Sheets? ›

1. Select the range where you want to replace values (here, B2:B19), and in the menu, go to Edit > Find and replace (or use the keyboard shortcut CTRL + H). 2. In the Find and replace window, enter a value you want to find (Web camera), and a value that will replace it (Camera), and click Replace All.

How do you use Find in sheet? ›

Use the keyboard shortcut Control + F (for Windows) and Cmd + F (for Mac). This will open a small Find box at the top right part of your sheet. Enter the string that you want to search in the entire worksheet.

How do I separate text and numbers in sheets? ›

Select the text or column, then click the Data menu and select Split text to columns... Google Sheets will open a small menu beside your text where you can select to split by comma, space, semicolon, period, or custom character. Select the delimiter your text uses, and Google Sheets will automatically split your text.

What is the formula to separate numbers from text in Excel? ›

Now you can use the functions =GETNUMBER or =GETTEXT just like any other worksheet function. If you often have a need to separate text numbers from cells in Excel, it would be more efficient if you copy these VBA codes for creating these custom functions, and save these in your Personal Macro Workbook.

How do I separate text and numbers in power query in Excel? ›

Split a column by number of characters
  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. ...
  2. Select the column you want to split. ...
  3. Select Home > Split Column > By Number of Characters.

How do you remove formula in Excel and keep value without copying? ›

Select the cell or range of cells that contain the formula.
...
To do that:
  1. Click a cell in the array formula.
  2. On the Home tab, in the Editing group, click Find & Select, and then click Go To.
  3. Click Special.
  4. Click Current array.
  5. Press DELETE.

How do you get rid of extra digits in Excel? ›

Round a number up by using the ROUNDUP function. It works just the same as ROUND, except that it always rounds a number up. For example, if you want to round 3.2 up to zero decimal places: =ROUNDUP(3.2,0) which equals 4.

How do I remove a specific number of characters in a string? ›

Using 'str.

replace() , we can replace a specific character. If we want to remove that specific character, replace that character with an empty string. The str. replace() method will replace all occurrences of the specific character mentioned.

What is the use of find and replace features in MS word? ›

Find and Replace in Word is a tool that searches a document for a specific word or phrase. You can use the tool to replace a word or phrase with another. You can review each instance of a word before replacing it, or replace all instances at once.

What is the difference between Find and Replace explain with example? ›

Find means to locate a word or phrase. Replace mean to change the word or phrase to a different word or phrase. Find and Replace means to find the word or phrase and substitute a word or phrase in it's place.

How do I find and replace in one column in Excel? ›

Re: Restricting Find/Replace to single column

Or if you want to replace values in a selected range, select all the cells where you want to replace a value and then invoke the replace window by pressing Ctrl+H.

What is an example of replace? ›

I replaced the old rug with a new one. They recently replaced the old phone system. The team's manager was replaced last season. The patient needed a transfusion to replace lost blood.

How replace function is used? ›

Returns a String in which a specified substring has been replaced with another substring a specified number of times. Required. String expression containing substring to replace.

How does replace work in Excel? ›

REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string. REPLACEB replaces part of a text string, based on the number of bytes you specify, with a different text string. Important: These functions may not be available in all languages.

How do you replace part of a string in Excel? ›

Find and Replace Feature
  1. Click and drag your mouse across multiple cells to limit the search to only those cells. ...
  2. Hold the "Ctrl-H" keys to open the "Find and Replace" window.
  3. Enter the text you wish to replace in the "Find What" field.
  4. Enter the text you wish to use for replacement in the "Replace With" field.

Which shortcut key is used to find and replace the text? ›

Tips: You can also open the basic Find and Replace pane with the keyboard shortcut CONTROL + H. When you replace text, it's a good idea to select Replace instead of Replace All.

How do you find and replace a character in a string in Excel? ›

To replace or substitute all occurrences of one character with another character, you can use the substitute function.

What is the Replace function in Excel? ›

REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.

Can you find and replace values within a formula? ›

Select the cells that have the formula in which you want to replace the reference. If you want to replace in the entire worksheet, select the entire worksheet. Go to Home –> Find and Select –> Replace (Keyboard Shortcut – Control + H).

How do you replace the first digit of a number in Excel? ›

Select a blank cell, enter formula =SUBSTITUTE(A1,"6","7",1) into the Formula Bar, and then press the Enter key. Notes: 1). In the formula, number 6 is the first number of referenced cells, and number 7 means than you need to replace the first number 6 with 7, and number 1 means only the first number will be changed.

What tab is Find and Replace in word? ›

1. Press Ctrl+H to open the Find And Replace dialog box or find the command on Home > Editing 2.

What is Find and Replace in MS word? ›

Find and Replace helps you to find words or formats in a document and can let you replace all instances of a word or format. This is particularly handy in long documents. To use Find and Replace, use the shortcut Ctrl+H or navigate to Editing in the Home tab of the ribbon, then choose Replace.

What is the use of Find and Replace feature of word processor? ›

Find and Replace is a function in Word that allows you to search for target text (whether it be a particular word, type of formatting or string of wildcard characters) and replace it with something else.

How do I find and replace between two characters? ›

Re: Extract text between two characters multiple times
  1. Press Ctrl+H to open the Find and Replace tool.
  2. In Find what box, type: |*;
  3. In Replace with box, type: , (Comma and space), and then press Replace All.

How do you find and replace in sheets? ›

Use find and replace in a spreadsheet
  1. On your computer, open a spreadsheet in Google Sheets.
  2. Click Edit. ...
  3. Next to "Find," type the word you want to find, If you want to replace the word, enter the new word next to "Replace with."
  4. To search for the word, click Find. ...
  5. Optional: Narrow your search by using an option below.

How do you find a text in a string in Excel? ›

On the Home tab, in the Editing group, click Find & Select, and then click Find. In the Find what box, enter the text—or numbers—that you need to find. Or, choose a recent search from the Find what drop-down box. Note: You can use wildcard characters in your search criteria.

How do you find and replace part of a formula in Excel? ›

You can use find and replace (Ctrl + Shift + H or on the Home Ribbon, click on the Find & Select button under editing). Once the form pops open, select Look In "Formulas" and then type in the Find what and Replace with values. e.g. Sales and New_Sales. You can choose to replace just the one found or replace all.

How do you find and replace only in a certain column? ›

If you want to restrict Find & Replace in particular column then, you need to click the column's alphabet to select entire column, Find and Replace will operate within that column only. Note, for multiple column selection use Ctrl + select columns (as shown above ).

Videos

1. #7 Find or replace text and numbers on a worksheet Excel | Learn Excel | 2021
(The Perfect Tasks)
2. replace text and numbers on a worksheet in excel
(excel formulas)
3. FIND OR REPLACE TEXT AND NUMBERS ON A WORKSHEETS| EXCELS TIPS
(MS-EXCEL BASICS)
4. Find or replace text and numbers in Microsoft Excel
(Microsoft 365)
5. Find or replace text and numbers on a worksheet | Excel tricks | Excel Malayalam |
(MH36 Excel)
6. Microsoft Excel 2016 - Using the REPLACE() Function
(Optimum Technology Transfer)
Top Articles
Latest Posts
Article information

Author: Kareem Mueller DO

Last Updated: 01/28/2023

Views: 5830

Rating: 4.6 / 5 (66 voted)

Reviews: 81% of readers found this page helpful

Author information

Name: Kareem Mueller DO

Birthday: 1997-01-04

Address: Apt. 156 12935 Runolfsdottir Mission, Greenfort, MN 74384-6749

Phone: +16704982844747

Job: Corporate Administration Planner

Hobby: Mountain biking, Jewelry making, Stone skipping, Lacemaking, Knife making, Scrapbooking, Letterboxing

Introduction: My name is Kareem Mueller DO, I am a vivacious, super, thoughtful, excited, handsome, beautiful, combative person who loves writing and wants to share my knowledge and understanding with you.