Search for lowercase followed by uppercase and insert paragraph

[jgwors]

Hi,
I have text in a spreadsheet imported from another application. Line breaks
have been removed so I have text like the following in cells
"lowercaseUppercase". Is there any way to find each occurrence of the
transition from lower to uppercase and insert a paragraph so the uppercase
text begins on a new line? This could be a vba macro is it can't be done
using standard commands.

 

[Ron Rosenfeld]

Hi,
I have text in a spreadsheet imported from another application. Line breaks
have been removed so I have text like the following in cells
"lowercaseUppercase". Is there any way to find each occurrence of the
transition from lower to uppercase and insert a paragraph so the uppercase
text begins on a new line? This could be a vba macro is it can't be done
using standard commands.

This can be done with a VBA Macro. The one below inserts the processed string
into the adjacent cell, but could be re-written to replace the initial cell
once you have it working properly.

I also "autofit" at the end. You may or may not want that.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range you want to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===================================
Option Explicit
Sub CR()
Dim c As Range, rg As Range
Dim re As Object
Const sPat As String = "([a-z])([A-Z])"
Const sRes As String = "$1" & vbLf & "$2"

Set rg = Selection
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = False
.Pattern = sPat
End With

For Each c In rg
c.Offset(0, 1).Value = re.Replace(c.Value, sRes)
Next c

'Size cells
rg.Offset(0, 1).EntireRow.AutoFit
rg.Offset(0, 1).EntireColumn.AutoFit
End Sub
=======================================
--ron

 

[jgwors]

Hi,
I have text in a spreadsheet imported from another application. Line breaks
have been removed so I have text like the following in cells
"lowercaseUppercase". Is there any way to find each occurrence of the
transition from lower to uppercase and insert a paragraph so the uppercase
text begins on a new line? This could be a vba macro is it can't be done
using standard commands.

This can be done with a VBA Macro. The one below inserts the processed string
into the adjacent cell, but could be re-written to replace the initial cell
once you have it working properly.

I also "autofit" at the end. You may or may not want that.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range you want to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===================================
Option Explicit
Sub CR()
Dim c As Range, rg As Range
Dim re As Object
Const sPat As String = "([a-z])([A-Z])"
Const sRes As String = "$1" & vbLf & "$2"

Set rg = Selection
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = False
.Pattern = sPat
End With

For Each c In rg
c.Offset(0, 1).Value = re.Replace(c.Value, sRes)
Next c

'Size cells
rg.Offset(0, 1).EntireRow.AutoFit
rg.Offset(0, 1).EntireColumn.AutoFit
End Sub
=======================================
--ron
.
Thanks for the reply. It did the trick.

 

[Ron Rosenfeld]

Hi,
I have text in a spreadsheet imported from another application. Line breaks
have been removed so I have text like the following in cells
"lowercaseUppercase". Is there any way to find each occurrence of the
transition from lower to uppercase and insert a paragraph so the uppercase
text begins on a new line? This could be a vba macro is it can't be done
using standard commands.

This can be done with a VBA Macro. The one below inserts the processed string
into the adjacent cell, but could be re-written to replace the initial cell
once you have it working properly.

I also "autofit" at the end. You may or may not want that.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the range you want to process. Then
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===================================
Option Explicit
Sub CR()
Dim c As Range, rg As Range
Dim re As Object
Const sPat As String = "([a-z])([A-Z])"
Const sRes As String = "$1" & vbLf & "$2"

Set rg = Selection
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = False
.Pattern = sPat
End With

For Each c In rg
c.Offset(0, 1).Value = re.Replace(c.Value, sRes)
Next c

'Size cells
rg.Offset(0, 1).EntireRow.AutoFit
rg.Offset(0, 1).EntireColumn.AutoFit
End Sub
=======================================
--ron
.
Thanks for the reply. It did the trick.

You're welcome. Glad to help. Thanks for the feedback.
--ron