Select cells with text

  • Thread starter Thread starter jeffrey
  • Start date Start date
J

jeffrey

I have an excel table with numbers and text. I would like to replace
all of the cells that contain text with NULL and keep all the cells
with numbers. (None of the cells have both numbers and text. Cell
either has text or numbers)

All of the cells are formatted as General, so I can't use the Find
function for text cells. I also couldn't figure out how to do with
with Go To.

Does anyone know how to do this?
Jeff
 
I have an excel table with numbers and text. I would like to replace
all of the cells that contain text with NULL and keep all the cells
with numbers. (None of the cells have both numbers and text. Cell
either has text or numbers)

All of the cells are formatted as General, so I can't use the Find
function for text cells. I also couldn't figure out how to do with
with Go To.

Does anyone know how to do this?
Click to expand...

Something like this maybe...

Range("A1:F10").SpecialCells(xlCellTypeConstants, xlTextValues).Clear

where you would change my assumed A1:F10 address to the actual address range
of your table.

Rick Rothstein (MVP - Excel)
 
Hi Jeff,

Am Tue, 5 Jul 2011 11:42:23 -0700 (PDT) schrieb jeffrey:
All of the cells are formatted as General, so I can't use the Find
function for text cells. I also couldn't figure out how to do with
with Go To.
Click to expand...

Find & Select => Go to special => Constants => Text


Regards
Claus Busch
 
Something like this maybe...
Range("A1:F10").SpecialCells(xlCellTypeConstants, xlTextValues).Clear

where you would change my assumed A1:F10 address to the actual
address range of your table.
Click to expand...

I should have included an error trap just in case the table has no text in
it...

On Error Resume Next
Range("A1:F10").SpecialCells(xlCellTypeConstants, xlTextValues).Clear
On Error GoTo 0

Rick Rothstein (MVP - Excel)
 
Hi Rick,
Thanks for the code!

Jeff

I should have included an error trap just in case the table has no text in
it...

On Error Resume Next
Range("A1:F10").SpecialCells(xlCellTypeConstants, xlTextValues).Clear
On Error GoTo 0

Rick Rothstein (MVP - Excel)
Click to expand...
 
Back
Top