CamelCase to Proper Case

[scorpio_libra]

I Am having a data of around 1000 rows. The data is in a camel case for e.g. SanFrancisco, UnitedArabEmirates...etc....i want to convert it into Title case and wherever the formula finds the Capital alphabet prior to that it should leave a single space......in simple terms.....
SanFrancisco should be changed to San Francisco
UnitedArabEmirates should be changed to United Arab Emirates

Already am having a macro for this, however am curious to know that it can be done by Excel Formula if any or not....

 

[dafan]

Well I came to this but it doesn't work with UAE for example...SF works fine:
=LEFT(A1,LOOKUP(2^15,FIND($D$1:$D$26,A1,2))-1)&" "&RIGHT(A1,LEN(A1)-LOOKUP(2^15,FIND($D$1:$D$26,A1,2))+1)

A1 holds the string, D1:D27 hold A-Z in capital.
Maybe anyone can finish this for me, I'm stuck.

 

[stanleydgromjr]

scorpio_libra,

Excel Workbook
	A	B
1	SanFrancisco	San Francisco
2	UnitedArabEmirates	United Arab Emirates
Sheet1

The Function/formula in cell B1 (copied down):
=AddSpace(A1)



Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Option Explicit
Function AddSpace(txt As String) As String
'
' If cell A1 contains: SanFrancisco
' cell B1: =AddSpace(A1)
' cell B1: San Francisco
'
Dim Hold As String, i As Long
Hold = Left(txt, 1)
For i = 2 To Len(txt) Step 1
  If Asc(Mid(txt, i, 1)) > 96 Then
    Hold = Hold & Mid(txt, i, 1)
  Else
    Hold = Hold & " " & Mid(txt, i, 1)
  End If
Next i
AddSpace = Hold
End Function

Have a great day,
Stan

 

[Scott Huish]

With a UDF:

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

Excel Workbook
	A	B
1	UnitedArabEmirates	United Arab Emirates
2	SanFrancisco	San Francisco
Sheet1

 

[stanleydgromjr]

HOTPEPPER,

Very nice......

What a great site for learning something new.


Thanks, and have a great day,
Stan