How to replace values less than 10 in several columns with "<10"?

Question

I have a dataset of over 10,000 and I was wondering how to suppress the file by replacing values that are less than 10 with <10?

I tried using the IF function: =IF(G4:I22726<10, "<10")

but it gave me an error.

The range G4:I22726 are the columns of the the table that contains numbers.

I would appreciate any help, thanks.

Answer 1

Without altering the actual cell contents, the appearance of "<10" can be achieved for values under 10 with custom formatting, such as:

[<10]"<10";General  

Answer 2

You can do this in one of two ways.

1) create a new range with a formula that computes the value you want; then copy the range, do a "paste-special-values" on top of the old data, and finally delete your temporary range; or
2) Use a small VBA macro:

Sub lessThan()
On Error Resume Next
For Each c in Range("G4:I22726").Cells
  if c.Value < 10 Then c.Text = "<10"
Next
End Sub

Run this macro just once and you're done.

The reason your original attempt generated an error is that your formula was referencing itself - that is what Excel calls a circular reference. A formula cannot depend on its own result, as it might never finish calculating (although in this case it would).

UPDATE
Showing how using a formula would work:

The formula shown in the formula bar was entered in cell C1, then dragged to D1. Finally I double-clicked the little box in the bottom right hand corner which automatically copied the formula all the way down to the bottom of the list (in this case, row 5; in your case it would copy down 22k rows).

Then you copy the selected range, and paste-special-values it on top of cell A1 (it will overwrite everything else). After that, delete columns C and D.