# Insert Space before Capital Letter

#### sweater_vests_rock

##### Well-known Member
I have data that comes into a sheet like

CountryCode | DateLastAccessed |

I would like to insert spaces before each capitalized letter. in the example,

Country Code | Date Last Accessed |

I've thought about using the Chr code in VBA, looping through each string, but I feel like this could be handled with more finesse. Ideas?

Thanks. Ben.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi Ben

Code:
``````Function InsSpaceB4Cap(ByVal myStr As String) As String
Dim b() As Byte, i As Long, Str As String
b = StrConv(myStr, vbFromUnicode)
For i = LBound(b) To UBound(b)
Select Case b(i)
Case 65 To 90
Str = Str & " " & ChrW\$(b(i))
Case Else
Str = Str & ChrW\$(b(i))
End Select
Next
InsSpaceB4Cap = Str
End Function``````

Best regards

Richard

well, i don't know about finesse Richard, but it is nice to have someone do the work for you

cheers. ben.

Hi Ben

Code:
``````Function InsSpaceB4Cap(ByVal myStr As String) As String
Dim b() As Byte, i As Long, Str As String
b = StrConv(myStr, vbFromUnicode)
For i = LBound(b) To UBound(b)
Select Case b(i)
Case 65 To 90
Str = Str & " " & ChrW\$(b(i))
Case Else
Str = Str & ChrW\$(b(i))
End Select
Next
InsSpaceB4Cap = Str
End Function``````

Best regards

Richard

This doesn't use the Chr function:

Code:
``````Sub T()
Const Str As String = "CountryCode | DateLastAccessed |"
Dim i As Integer
Dim Txt As String
For i = Len(Str) To 2 Step -1
If Mid(Str, i, 1) Like "[A-Z]" And Mid(Str, i - 1, 1) <> " " Then
Txt = " " & Mid(Str, i, 1) & Txt
Else
Txt = Mid(Str, i, 1) & Txt
End If
Next i
Txt = Left(Str, 1) & Txt
MsgBox Txt
End Sub``````

I don't know a way to avoid a loop.

Andrew.
This doesn't use the Chr function: [snip]
Well done! Bummer about the loop, but sometimes that is the way to go.

I like your use of the Like operator (or function?). I don't think I've ever seen documentation that makes me feel like I have command of it, however -- any suggestions for learning more?

cheers. Ben.

Neither does this (use Chr):

Code:
``````Function InsSpace(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "([A-Z])"
.Global = True
InsSpace = .Replace(txt, " " & "\$1")
End With
End Function``````

Richard

There, we've (with a lot of work by you ) done it! Thanks to you both!

Neither does this:

Code:
``````Function InsSpace(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "([A-Z])"
.Global = True
InsSpace = .Replace(txt, " " & "\$1")
End With
End Function``````

Richard

A word of warning to those who may come after...none of these codes handle situations where some words have spaces and some do not. But then, you wouldn't want us to do everything for you, right? where is the fun in that?

cheers. ben.

A word of warning to those who may come after...none of these codes handle situations where some words have spaces and some do not. But then, you wouldn't want us to do everything for you, right? where is the fun in that?

cheers. ben.

Actually, my code doesn't add a space if one already exists before the capital letter.

This is a solution to the spaces issue for my latter function:

Code:
``````Function InsSpace(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "([A-Z])"   '() are needed to return a back reference
.Global = True
InsSpace = .Replace(txt, " " & "\$1") 'example of a backreference
End With
InsSpace = Application.WorksheetFunction.Trim(InsSpace)
End Function``````

Richard

EDIT: Another modification that seems to work currently (and is slightly more efficient):

Code:
``````Function InsSpace(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "(\B[A-Z])"
.Global = True
InsSpace = .Replace(txt, " " & "\$1")
End With
End Function``````

well there you have it, just no fun at all.
apologies andrew, i looked at your solution too hastily.

cheers. ben.

Replies
9
Views
293
Replies
4
Views
310
Replies
2
Views
877
Replies
17
Views
622
Replies
3
Views
555

1,219,811
Messages
6,150,366
Members
450,955
Latest member
rose8693

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back