Deleting text only

[deymongussman]

Hi:

I have several columns that has text, and numbers. There is no combination of text and numbers. What is the easiest way to get rid of the text, but keep all the numbers. Thanks for your help.

Deymon

 

[Scott Huish]

Change the range in the set s line and perhaps:

Sub test()
On Error Resume Next
Dim r As Range, s As Range
Set s = Range("A:D")
Set r = s.SpecialCells(xlCellTypeConstants, xlTextValues)
r.ClearContents
Set r = s.SpecialCells(xlCellTypeFormulas, xlTextValues)
r.ClearContents
End Sub

 

[deymongussman]

HOTPEPPER: Thanks. Works perfectly. How can this be done using just a formula.

Deymon.

 

[Scott Huish]

A formula can't edit the contents of a different cell than the formula is in.

But if you want to use a helper column, and then do a copy and Paste Special Values

=IF(ISNUMBER(A1),A1,"")

Copy down.

Do a Copy on this Column, then do a Paste Special Values on this column, then delete the original column.

 

[deymongussman]

HOTPEPPER: thanks again. I came up with a formula similar to yours and I could not figure out why it was not working. My formula - IF(ISNUMBER(D5),"",D5). After seeing your formula, I realise my stupid mistake.


Deymon

 

[Milo_Minderbinder]

You can also use Edit->Go to->Special and choose either Constants or Formulas and select "Text" only.

Then delete the selected cells.