ExcelBanter - View Single Post - search for any lowercase immediately followed by an UPPERCASE

Ron Rosenfeld

On Sat, 03 Nov 2007 18:52:40 -0000, wrote:

This works perfectly!

You have to make sure you have a 'finite' selection instead of
clicking the column header to select the entire column or it will fun
forever.

Thanks very much!!

Thanks for the feedback. Glad to help.

I cannot reproduce your problem by selecting a column header.

It does take longer with blank rows than if all the rows are filled with a
single, short, garbage phrase. But even with all the cells empty, it still
only took about 10-12 seconds to run.

A small modification makes it do an entire blank column much quicker -- about
six seconds on my machine.

================================
Option Explicit
Sub InsSpc()
Dim c As Range
Dim sTemp As String
Dim re As Object
Const sPat As String = "([a-z])([A-Z])"
Const sRes As String = "$1 $2"

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat

'you can change Selection to a defined range
'or rewrite this as a function
'Be sure to backup data before running

For Each c In Selection
sTemp = c.Text
If re.test(sTemp) = True Then _
c.Value = re.Replace(sTemp, sRes)
Next c

End Sub
=================================

An additional approach would be to change the selection to include only those
cells with data.

--ron