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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top