Find and Replace only values in Excel (not within formulas)

Question

I have a spreadsheet pictured below that I'm trying to do a find and replace. For example, I'd like to replace any value in the Last column tht's (4.2800) with a different value.

When I just try and do a search for that value, it will correctly find all the values:

However, every time I try to replace the value - it only gives me the option to search within the formulas:

I was wondering if anyone had any insight onto why this was happening? An image of my spreadsheet is below.

Example data:
+-----------+--------------+
|   Last    |  Settlement  |
+-----------+--------------+
|           |              |
|           |              |
|           |              |
|           |              |
|  (0.0062) |  (0.0075)    |
|           |              |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|           |              |
|           |              |
|           |              |
|           |              |
|           |              |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  (4.2700) |  (4.4200)    |
|           |              |
|           |              |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  59.8800  |  59.3400     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |
|  54.9100  |  54.1700     |

Answer 1

My workaround for this is:

1. Use the Find tab and Find All (Values). (Tick "Match entire cell contents" if you only want cells with an EXACT value to be replaced. For example 3.53, but not 13.53.)
2. Switch to Replace tab, change "Find what:" text box to "*" (without the quotes).
3. Click the first item in the found items box, scroll to the bottom and Shift-click the bottom item in the found items box.
4. Enter the "Replace with:" value, and click Replace All.

Answer 2

As far as I know, the Replace option has always had only the Formula option. Unfortunately, if you just select that option, you will not only replace the values (which are equivalent to formulas), but also any value that happens to be a part of a formula (as, for example, part of a cell reference).

One work-around:

• Select the Find option with values and Find All
• Switch to the Replace tab
  • The found references should still be listed
• Enter your replace with value
• Select the locations listed in the find all box
  • This limits the Replace option to those cells only
• Replace all

Note If the values you are replacing are numbers, and not text strings, be sure to change the Find what: in the Replace dialog with the unformatted version of those numbers (what you would see in the formula bar or with the General format type).

So in your second screenshot, you can probably just hit Replace All but in other circumstances, you might want to select all those cells found (select a cell in the range and then ctrl+A)

Given your example data, you can just Replace with formulas and it will work, provided you use the non-formatted value:

Before

After