# Clear cell contents based on a range with special characters (underscores)

#### naryfa

##### New Member
Hello,

I'm a total noob when it comes to this so please be kind.

I need to clear the contents (not delete rows) in a column A. The contents contain numbers from 1 to 99, as well as something like 5ú _ or 4_ as well as ===== , ------- or occasional G.

These are generated by an old DOS program, and need to be cleared because they confuse the person reading the report.

In other words. Only the 3-digit numbers need to remain, everything else must go. Of course, this can be done by hand, but this report has to be run every day, and the sample below is just 1/20th of the whole thing...

Any help would be appreciated.

Sample:
 A DRI D === 111 4ú --- 116 5ú G 5ú G 65 --- 122 5ú 65 --- 129 4_ --- 135 5ú 65 --- 223 2ú --- 242 5ú G 5ú G 65 --- 285 5ú 65 --- 509 65 --- 524 65 --- 556 DR D === 2ú --- 558 5ú G 5ú G 3ú --- --- DR D === 691 5ú G === (c)

<colgroup><col></colgroup><tbody>
</tbody>

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Try in B1:

=IFERROR(IF(AND(A1/100>=1,A1/100<10),A1,""),"")

This will leave the cell appear blank if it sees anything other than a 3 digit number.

It worked for B1, thank you. Just how do I apply that to the whole column?

Try in B1:

=IFERROR(IF(AND(A1/100>=1,A1/100<10),A1,""),"")

This will leave the cell appear blank if it sees anything other than a 3 digit number.

Just fill down as far as you have data in "A". Hover cursor on bottom right hand corner of cell then double click.

Okay, thank you very much!

Replies
7
Views
188
Replies
6
Views
505
Replies
3
Views
356
Replies
17
Views
892
Replies
3
Views
405

1,219,569
Messages
6,149,040
Members
450,853
Latest member
xtiinctt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back