Dealing with Text Strings in a cell.

[MuskyMan]

I'm going to be dealing with a rather large spreadsheet monthly (8000+rows).

Cells in column "C" have both numbers and text strings. I need to convert the text strings to ZERO. I can't find the correct sytax for the If Then Loop to make the change. I keep getting a Type Mismatch error.

Thanks in advance for your help.

 

[Mikeb1970]

Hi MuskyMan,


Maybe some more info would help here?

an example perhaps

With regards

Mike

 

[MuskyMan]

Mike,

Cell(1,"C&quot.value=100
Cell(2,"C&quot.value=525
Cell(3,"C&quot.value="Nothing to report"
Cell(4,"C&quot.value=1500
.
.
Cell(8524, "C&quot.value="Nothing"

Row.count can be as high as 9100 and I need a loop to convert the text string (such as Cell 3, C above) to 0.

Hope the above helps..

 

[Mikeb1970]

Hi again MuskMan,

This is a question that should be posted in VBA for applications, there are ways to do this not using code, but they are to complicated imo

lett skip, ken, tony, xlbo, bleufdragon , .....
take a crack at this, they will help you in a jiffy


With regards

Mike

 

[TonyJollans]

Hi MuskyMan,

In D1, put ..

[blue]

=IF(ISNUMBER(C1),C1,0)

[/blue]

Copy it down as far as your data in column C.

Now Select column D,
Edt > Copy
Edit > Paste Special > Values > OK
And Delete Column C

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.