Manually finding and replacing parts of text can be exceedingly tedious for lengthy Excel spreadsheets. Luckily, Microsoft included a couple of useful tools for locating text parts and replacing them with your preferred selection. Excel's "Find and Replace" feature will search an entire spreadsheet or selected cells for your specified text. Excel also offer a "Replace" function. When combined with the "Find" and "Len" functions, the "Replace" function allows you to dynamically find and replace text based on entered values or the contents of another cell.
Find and Replace Feature
Click and drag your mouse across multiple cells to limit the search to only those cells. If you wish to perform a sheet-wide search, do not select multiple cells.
Hold the "Ctrl-H" keys to open the "Find and Replace" window.
Enter the text you wish to replace in the "Find What" field.
Enter the text you wish to use for replacement in the "Replace With" field.
Click "Replace All" to find and replace each instance of the text part located in the selected cells or entire sheet. Alternatively, click "Replace" to find and replace the first instance of the text in your currently selected cell. Click "Close" when completed.
Enter "=REPLACE(source,start_number,length,replacement)" without quotes in an empty cell. The source and replacement can be a cell reference or a text string surrounded by quotation marks. The start_number and length describe where and how much text is replaced. As an example, "=REPLACE(A1,3,1,"hn")" looks in cell A1 and replaces one character of text, starting at the third character, with "hn." If cell A1 contains "Jon Doe," it is corrected to "John Doe."
Replace "start_number" with "FIND(find_text,source)" without quotes to locate the starting position for the "Replace" function. Continuing with the example, the "Find" function in the formula
looks in cell A1 for the text "n" and sends "3" to the "Replace" function. The "find_text" variable can also be a cell reference.
Replace "length" with "LEN(find_text)" without quotes to determine the length of text needing to be replaced. Continuing with the example, the "Len" function in the formula
counts the number of characters in "n" and outputs "1" to the "Replace" function. The "find_text" variable can also be a cell reference. If cell B1 contained the letter "n," the formula
would produce the same results to correct "Jon Doe" to "John Doe."
- Visage/Stockbyte/Getty Images