how to delete values with no decimal points?

[pooja2010]

Hello all,

I have a huge excel file with several rows and columns with quantitative values, which looks like follows..

[TABLE="width: 416"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 416"]
<colgroup><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]0.551124554119557[/TD]
[TD]0.311702687666443[/TD]
[TD]0.657182579301094[/TD]
[TD]0.85164658389987[/TD]
[TD]0.656711634907342[/TD]
[/TR]
[TR]
[TD]0.503733888018934[/TD]
[TD]0.207373816284868[/TD]
[TD]0.575716114028629[/TD]
[TD]0.861585947213311[/TD]
[TD]0.62342783542161[/TD]
[/TR]
[TR]
[TD]0.483966195579803[/TD]
[TD]0.337281494970849[/TD]
[TD]0.570984391170453[/TD]
[TD]0.920644219462952[/TD]
[TD]0.64148173879859[/TD]
[/TR]
[TR]
[TD]0.0625304746812299[/TD]
[TD]0.163989435051611[/TD]
[TD="align: right"]1,00E+14[/TD]
[TD]0.812825049281041[/TD]
[TD]0.088216346651155[/TD]
[/TR]
[TR]
[TD]0.0819582470926379[/TD]
[TD]0.196355478526973[/TD]
[TD="align: right"]1,00E+14[/TD]
[TD]0.873711792962655[/TD]
[TD]0.0555090833383204[/TD]
[/TR]
[TR]
[TD]0.52130847012177[/TD]
[TD]0.285880332763841[/TD]
[TD]0.640743847618109[/TD]
[TD]0.895934284987955[/TD]
[TD]0.617790210479352[/TD]
[/TR]
[TR]
[TD]0.0600770003231761[/TD]
[TD]0.175386231310059[/TD]
[TD="align: right"]1,00E+14[/TD]
[TD]0.908755036881362[/TD]
[TD]0.0449497976538427[/TD]
[/TR]
[TR]
[TD]0.470995386221247[/TD]
[TD]0.252821122012323[/TD]
[TD]0.568049390944819[/TD]
[TD]0.818177539544879[/TD]
[TD]0.60534403964158[/TD]
[/TR]
[TR]
[TD]0.527184861383956[/TD]
[TD]0.282063692615608[/TD]
[TD]0.655431692803321[/TD]
[TD]0.840409445091711[/TD]
[TD]0.623304188292984[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The file is really huge and I need it for my analysis. However i have some values which are wrong. The values like "1,00E+14" are spread randomly across this huge file and I cannot look for them and delete one by one. Is there a way or a code with which excel can find and delete these wrong numbers across the whole file?

Your help appreciated.
Thanking you.

 

[Ron Coderre]

EDITED TO OFFER A SOLUTION THAT MATCHES YOUR DESCRIPTION OF UNWANTED CELL CONTENTS

In your sample the cells contained values like this: 1,00E+14.
If that's accurate, try this

• Select the range of data cells
• CTRL+H
...Find: ,
...Replace with: KEEP
...Click: Replace all
...Click: Close

• Press F5
...Click: Special
...Check: Constants
...Leave only TEXT checked
...Press: Delete

That should remove all cells that contain a comma.


Does that work for you?

 

[pooja2010]

Hi Ron,

Thanks for your reply. I had tried that. But the excel does not seem to search for the comma "," .. Says "excel cannot find the data you are searching for".. Is there any other way you can know of ?

 

[nuked]

Something like this? Assuming all 'wanted' values are less than 1. Amend as necessary.

Option Explicit
Sub GetRid()
Dim cel As Range
For Each cel In ActiveSheet.Cells
    If Not IsNumeric(cel) Or cel.Value > 1 Then
        cel = ""
    End If
Next cel
End Sub

 

[kevatarvind]

Sub Remove_Unwanted()
Dim rmv As Range
For Each rmv In ActiveSheet.UsedRange
    If Not IsNumeric(rmv) Or rmv.Value > 1 Then
        rmv = ""
    End If
Next rmv
End Sub

 

[Ron Coderre]

Yes, I don't know what I was thinking when I posted that

This should work:
• Select the range of data cells
• CTRL+H
...Find: . <----Period or whatever your separator is
...Replace with: KEEP
...Click: Replace all
...Click: Close
That converts cells with decimals to text values


• Press F5
...Click: Special
...Check: Constants
...Leave only NUMBER checked
...Press: Delete

That should remove all cells that displayed a comma.

• Select the range of data cells (again)
• CTRL+H
...Find: KEEP
...Replace with: . <----Period or whatever your separator is
...Click: Replace all
...Click: Close

that restores the text cells back to numeric.

Does that help?