Removing all lowercase/non-capitalized words in a sheet

[mlarson]

Hi all! I'm hoping you can help me.

I would like all lowercase/non-capitalized words to be removed from my active sheet (rows 2 to 500), with only capitalized words remaining. Is there a macro/VBA or formula to accomplish this task?

For example: Is Vienna a city in Austria? (in cell B7)
Desired output/result: Is Vienna Austria (in cell B7, the same cell)

Another example: where do John and I go to school? (in cell C5)
Desired output/result: John I (in cell C5)

I hope I am explaining this clearly. Thanks in advance for your help!

 

[mlarson]

Hi Micron, I don't mind it keeping numbers.

I've been using the code that Herakles provided and it was working really well for about an hour... running it every 5-10 minutes or so. But now it seems like it is running an endless loop the last few times I've tried.

Anybody have thoughts on why that might be? Cleared some memory from my computer but that didn't seem to help.

Thanks.

 

[mlarson]

It just gave me this debug/error...
If Len(rngCell) > 0 Then

Run-time error '13':
Type mismatch

It wasn't giving that before, it would just run forever. Thoughts?

 

[Micron]

My guess based on that is if rngCell is null/empty it becomes Nothing thus you cannot use functions such as Left, Len etc. on it. The root of the problem might be due to using UsedRange as it includes cells with formatting and such even if they have no data.

 

[mlarson]

OK, thank you.