Excel tutorials

How to find and select all cells that contain a number or value in a data range

If you are in a situation where your data range contains numbers in multiple cells and you want to replace them with text or a specific letter, see this tutorial, how to do so. The situation is shown in the picture below. So imagine having a few hundred lines and columns containing cells with text and numbers. You want to replace all cells in the data range with one and the same data (text or letter).

Replace multiple cells containing a number

Step 1.

The first step is to find and select all cells containing a number or values.

- Selection the entire data range and press 'F5' on the keyboard later (option: press 'CTRL+G' shortcut). Now you have an open window like in the picture below. Click the 'Special' button.

Go To window in Excel

Step 2.

On the next window "Go To Special", mark radio button "Constants" and check box "Numbers". Click OK button.

Go To Special window in Excel

The shorter path to open the 'Go To Special' window is via the command - > 'Find & Select' - > 'Go To Special' on Excel HOME Ribbon/Tab.

Step 3.

Now you have a situation like in the picture below. Note that all cells containing a number or value have been selected.

Selected all cells containing a value

Step 4.

Press the 'F2' key on the keyboard. Note that the cursor to modify the data in the cell is located in the first 'B3' cell. Delete a number or value and type text such as 'changed'. Of course, you can enter any text or value or number you want to set as a new data.

Modify data in multiple cells in a single move

Press CTRL+ENTER together. Now all values in all selected cells have been modified with new data. (picture below).

Convert a number or value to text in multiple cells at once

Modified cells in a single move

Find all cells containing a value and replace them with text using a formula

If you want to find and replace all cells containing a number, you can do so by using an Excel formula. In a situation when the source data is on the worksheet 'Sheet1', set the formula below to another worksheet and copy it to the right and down.

=IF(ISNUMBER(Sheet1!A1);"changed";Sheet1!A1)

This way you converted all the numbers into text. You must convert all data on Sheet2 to a text value that does not have a formula (Copy - > Paste Special - > Values).

How to find all cells that contain a number and convert them to text using a VBA macro

Above the above example you can work with VBA. You need to copy this VBA code below to 'Code Window' of your 'VBE for Excel'. (by author: alansidman).

Sub ReplaceAnyNumbersCell()
Dim c As Range
Dim rng As Range
Set rng = Range("A1").CurrentRegion
For Each c In rng
If IsNumeric(c) Then c = "P"
Next c
End Sub

And the nake in the attachment of this tutorial you have the possibility of a Download file that I was working on this tutorial.