In this post we will see how to split words in excel upon encountering an uppercase letter, for example if we have a text string having 3 uppercase characters in it then the new word will be split by upper case character as shown in the example below.
Column B shows the new text string split by uppercase character, kindly note that if the text string doesn’t have ay uppercase character then the words wont split..
Step 1
Open VB editor from Developer tab in excel as shown in the figure.
Step 2
Insert a module in which we will write a VBA function that will split excel string by uppercase character .
Step 3
Now double click the newly created module to open that and paste the below code in the module.
Function Split_by_Uppercase(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject(“vbscript.regexp”)
With objRegex
.Global = True
.Pattern = “([a-z])([A-Z])”
SplitCaps = .Replace(strIn, “$1 $2”)
End With
End Function
This VBA function will take the cell value as argument and split the word whenever an uppercase letter is encountered, in the output you can see the we have got the result which is separated by uppercase letter.
Hope this helped.
