Split Words In Excel If Uppercase Character Is Found

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.

Excel text string to be split when an uppercase letter is found

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.

Open developer tab to write function to split words upon encountering capital character

Step 2

Insert a module in which we will write a VBA function that will split excel string by uppercase character .

Insert a module in the VB editor

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

VBA code to split text string if an upper case character is found

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.

Excel cell is split by capital letter

Hope this helped.

Share The Knowledge

Random Posts