Skip to content

Instantly share code, notes, and snippets.

@ateneva
Last active April 16, 2017 15:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ateneva/8c90c3c3985ba8696650b874856cea8a to your computer and use it in GitHub Desktop.
Save ateneva/8c90c3c3985ba8696650b874856cea8a to your computer and use it in GitHub Desktop.
Insert a space after the first uppercase character it finds in a string
Sub FindFirstUpperCharacter()
Dim mStr As String
Dim FindUpper As String
Dim FindLower As String
Dim i As Integer
Dim Cell As Range
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'assumes the string has only two Upper characters that need separating by blank space; Angelina Teneva, March 2017
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Cell In ActiveSheet.Range("G3:G43")
mStr = Cell.Value
For i = 2 To Len(mStr)
If Mid(mStr, i, 1) Like "[A-Z]" Then
FindUpper = i
Cell.Value = Left(mStr, FindUpper - 1) & Chr(32) & Right(mStr, Len(mStr) - FindUpper + 1)
Exit For
End If
Next i
Next Cell
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment