Removing text from cells in a spreadsheet

[rickcb]

I deal with very large spreadsheets containing extracted process data from a data logger(25000 rows and 250 columns) with Excel 2007. Some of the cells contain text messages indicating an error message recieved from the data logger. I would like to be able to remove this text from the cells and leave the cells blank automatically (with a macro that loads every time I open Excel).

Thanks in advance for your help.

 

[DonkeyOte]

what is the exact string you're looking to replace ?

 

[rickcb]

There are many text strings to remove i.e. Bad Input, Calc Failed, No Data etc...

 

[DonkeyOte]

Well, could you list them ?

Thanks

 

[rickcb]

I will be collecting this data from many different sources. Therefore, the error messages will be different from time to time. I was looking for something generic i.e any text appearing in the cell have to be removed, keeping only the numeric cells.

 

[DonkeyOte]

something like the below you mean ?

Dim rng As Range
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
rng.ClearContents

 

[rickcb]

That's somewhat what I'm looking for. I would like to choose the range of cells before I execute the code and have the macro replace the text into the selected range?

Thanks,

 

[DonkeyOte]

even simpler

Selection.SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents

 

[rickcb]

Thanks,

That's exactly what I've been looking for. That'll save me tons of time.

Rick