Deleting text only

deymongussman

Board Regular
Joined
Jul 25, 2004
Messages
112
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Change the range in the set s line and perhaps:

Code:
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
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jul 25, 2004
Messages
112
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

Well-known Member
Joined
Mar 2, 2006
Messages
548
You can also use Edit->Go to->Special and choose either Constants or Formulas and select "Text" only.

Then delete the selected cells.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,634
Members
410,804
Latest member
bluepinky
Top