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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You can also use Edit->Go to->Special and choose either Constants or Formulas and select "Text" only.

Then delete the selected cells.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top