Select cells with text

[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

 

[Rick Rothstein]

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?

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)

 

[Claus Busch]

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.

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


Regards
Claus Busch

 

[Rick Rothstein]

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.

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)

 

[jeffrey]

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)