Query - Split at capital letter in word

Hi SAM190370,

After research, I add a "/" before a Capital letter surrounded by small letters using VBA, then split the text using the "/" delimiter in Query Editor. Please follow the steps below.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Insert /  before each capital letter in a cell

Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
    Dim I As Integer
    SplitWords = Left(Str, 1)
    For I = 2 To Len(Trim(Str))
        If (Asc(Mid(Str, I, 1)) > 64) And _
           (Asc(Mid(Str, I, 1)) < 91) And _
           (Mid(Str, I - 1, 1) <> " ") Then _
            SplitWords = SplitWords & "/"
        SplitWords = SplitWords & Mid(Str, I, 1)
    Next
End Function

3. Save and close this code, go back to the worksheet, please enter this formula: =splitwords(A2) into a blank cell adjacent your data, see screenshot:



4. Get data from excel, and use the new column to split column. 



5. Creat custom column replace the value where New.2 equals to null. Then remove the New.1, order the columns, finally you get the expected result as the following screenshot.

add custom columnexpected result

Here is my Query statement, you can check it in details.

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-huizhn\Desktop\case status.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Column2", "New"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "New", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"New.1", "New.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"New.1", type text}, {"New.2", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [New.2] = null then null else [New.1]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Original", "New.1", "Custom", "New.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"New.1"})
in
    #"Removed Columns"

Best Regards,
Angelia

Hi Sören,

pls find the file enclosed with the 1st 200 rows of your table. It does what I expected it to do, but maybe my understanding was wrong:

https://1drv.ms/u/s!Av_aAl3fXRbehastWn_7iVvVOWbW8g