Fixing CamelCase (revisited)

[gpence]

I did a search and found a previous thread from March, 2009 which included this UDF to fix CamelCase strings:

Function FixSpacing(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "([a-z])([A-Z])"
    .Global = True
    FixSpacing = .Replace(r, "$1 $2")
End With
End Function

Unfortunately, this solution doesn't seem to address several issues I'm having with my data. My data points (which happen to be colors) can include numbers and acronyms which I'd like to maintain. Here is an example of some of my data:

CAMEL CASE	PROPER CASE
Cobalt	Cobalt
MidnightGreen	Midnight Green
DeepJungleGreen	Deep Jungle Green
AirForceAcademyBlue	Air Force Academy Blue
USAFABlue	USAFA Blue
CastletonGreen	Castleton Green
SacramentoStateGreen	Sacramento State Green
DarkGreen	Dark Green
DeepSkyBlue4	Deep Sky Blue 4
Gray44	Gray 44
SeaBlue	Sea Blue

Logically, I need to check to see if each letter is a lowercase, uppercase or numeric result, AND if there are 2 (or more) Uppercase letters or numbers adjacent to another, they should be kept together (no space added). That seems trickier with the "USAFA Blue" and "Gray 44" examples above

It's been years since I tried my hand at regex patterns. Can anyone help me out?

Many thanks,
gpence

 

[gpence]

OK, Rick. Here's a new twist. I have 1 entry that reads "AirForceBlue(RAF)" and another that reads "AirForceBlue(US)" and neither one renders the "(" with a space in front of it, so I get ...Blue(RAF) and ...Blue(US) when I want it show ...Blue (RAF) and ...Blue (US). Can you tell me how to do that?

Thanks,
gpence

 

[Rick Rothstein]

OK, Rick. Here's a new twist. I have 1 entry that reads "AirForceBlue(RAF)" and another that reads "AirForceBlue(US)" and neither one renders the "(" with a space in front of it, so I get ...Blue(RAF) and ...Blue(US) when I want it show ...Blue (RAF) and ...Blue (US). Can you tell me how to do that?

Give this function a try...

[table="width: 500"]
[tr]
	[td]Function FixSpacing(S As String) As String
  Dim X As Long
  For X = 2 To 2 * Len(S)
    If Mid(S, X - 1, 2) Like "[a-z)][A-Z0-9(]" Or Mid(S, X - 1, 2) Like "[a-z0-9][A-Z(]" Or Mid(S, X - 1, 3) Like "[A-Z][A-Z][a-z]" Then
      S = Application.Replace(S, X, 0, " ")
    ElseIf Mid(S, X - 1, 3) Like "[A-Z][A-Z]#" Then
      S = Application.Replace(S, X + 1, 0, " ")
    End If
  Next
  FixSpacing = S
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

You could also try changing the RegExp pattern (& the Replace line) as shown below.
I've also re-written your function a little so that the RegExp object does not have to be created every time the function is called. It should make the function somewhat faster.
For strings like the samples posted so far, my testing shows the RegExp function about 15% faster than the looping function.
If the strings get longer, the % advantage of the RegExp function should increase. For example, with strings about 70 characters long, for me the RegExp function was about 30% faster.

Anyway, you can give it a try and choose whichever suits you best (assuming they both do what is required).

If either function fails for other sample data, I'm sure if you post it and the expected outcome Rick & I would look at the functions to see if they could be adapted.

Function FixSpacing(r As String) As String
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "([a-z](?=\(?[A-Z\d]+)|[A-Z]+(?=[A-Z][a-z]))"
  FixSpacing = RX.Replace(r, "$1 ")
End Function

 

[Peter_SSs]

Actually, if speed is an issue for you, you'd get another 20-25% speed improvement with the code re-written as below.
However, until you are absolutely sure the Pattern is correct, I would comment out the Static line and un-comment the Dim line. That will slow the code, but if you happen to change the pattern while testing and you are using the Static line, the changed pattern would not get picked up - unless you manually ensure RX is set back to Nothing before running the function.

Once you are sure you have the pattern you want, change the Static/Dim lines back to how they are below.

Function FixSpacing(r As String) As String
  Static RX As Object
'  Dim RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "([a-z](?=\(?[A-Z\d]+)|[A-Z]+(?=[A-Z][a-z]))"
  End If
  FixSpacing = RX.Replace(r, "$1 ")
End Function

 

[MandeepBaluja]

Can you explain this to .Replace(r, "$1 $2") what is $1 and $2

 

[Peter_SSs]

Can you explain this to .Replace(r, "$1 $2") what is $1 and $2

Take the Pattern used in post #1
.Pattern = "([a-z])([A-Z])"

This pattern is looking to match a lower case letter followed by an upper case letter. Such a pattern could also be written as just "[a-z][A-Z]" but the addition of the parentheses in the original pattern breaks the expression into two sub-expressions "([a-z])([A-Z])"

In the Replace line $1 represents the first sub-expression & $2 represents the second sub-expression. the fact that the Replace line includes a space character "$1 $2" means that a space would be inserted between the lower case letter and the upper case letter.

 

[MandeepBaluja]

WOW', Fantastic Explanation