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

 

[Peter_SSs]

Welcome to the MrExcel board!

Try changing the pattern to:
"([a-z]|[A-Z]+)([A-Z]|\d)"

If that is not what you want, post a few examples & expected outcomes for which it fails.

 

[gpence]

Awesome, Peter. I don't see any data points where it fails. Can you explain the pattern to me?

 

[gpence]

Awesome, Peter. I don't see any data points where it fails. Can you explain the pattern to me?

Only 1 failure I see. AirForceBlueRAF came out as Air Force Blue RA F for some reason... a small manual fix which I am happy to make considering the 1,500+ data points I needed converted to proper case!

 

[Peter_SSs]

Try
"([a-z]|[A-Z]+)([A-Z].|\d)"

(a lower case letter OR any number of upper case letters) followed by ((an upper case letter followed by any character) OR a digit)

 

[gpence]

Of course, now my mind is thinking about why the "Air Force Blue RAF" came out as "Air Force Blue RA F" and what the implications would be if the string started with a digit... but that's for another day!

Many thanks again, Peter_SSs.

 

[Peter_SSs]

Many thanks again, Peter_SSs.

You are welcome.

 

[Rick Rothstein]

Here is another function that you can try... it appears to correctly handle all of the examples you posted so far... and I have tried to anticipate what I think you would want if there were leading or internal numbers in your text string.

[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]" Then S = Application.Replace(S, X, 0, " ")
  Next
  FixSpacing = S
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

... it appears to correctly handle all of the examples you posted so far...

Look more closely.

 

[Rick Rothstein]

Look more closely.

Yeah, I missed one... thanks for catching that. Below is my modified code that now appears to handle all the posted examples correctly. In addition, it further assumes that all numbers should be separated from any preceding letter by a space and a number should be separated from any upper case letter following it. So that, with those assumptions, these additional examples would be handled as shown...

USA123TEAMBlue ==> USA 123 TEAM Blue

AirForce123BlueRAF ==> Air Force 123 Blue RAF

123SeaBlue ==> 123 Sea Blue

Gray4x ==> Gray 4x

Here is my modified code...

[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]