Insert Space before Capital Letter

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Ben

How about the following:

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
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
well, i don't know about finesse Richard, but it is nice to have someone do the work for you :)

cheers. ben.

Hi Ben

How about the following:

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657

ADVERTISEMENT

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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657

ADVERTISEMENT

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.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
well there you have it, just no fun at all.
apologies andrew, i looked at your solution too hastily.

cheers. ben.
 

Forum statistics

Threads
1,141,300
Messages
5,705,577
Members
421,399
Latest member
hjweiss00

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top