Forum Discussion

SAM190370's avatar
SAM190370
Frequent Visitor
9 years ago
Solved

Query - Split at capital letter in word

Below you can find an example of a column from which I need to split in two columns.  The split should happen if a Capital letter is surrounded by small letters.  Column 1 and column 2 shows how it s...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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

  • SAM190370's avatar
    SAM190370
    Frequent Visitor
    9 years ago

    Hi v-huizhn-msft,

     

    I am full of admiration:-)  This will do the exact job I am looking for.  

     

    Thx.

     

    / Søren (Denmark)

    • ImkeF's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      9 years ago

      In defense of M, I'd like to post one of the possible approaches here:

       

      let

// Function to extract split position
SplitPos = (text) =>
let
    Source = Text.ToList(text),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom1" = Table.AddColumn(#"Converted to Table", "Number", each Character.ToNumber([Column1])),
    AddIndex = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1),
    #"Added Custom2" = Table.AddColumn(AddIndex, "Sum", each if [Index]>0 then if [Number]<91 and AddIndex[Number]{[Index]+1} >=91 and AddIndex[Number]{[Index]-1} >=91 then "Split" else null else null),
    Split = try Table.SelectRows(#"Added Custom2", each [Sum] = "Split")[Index]{0} otherwise null

in
    Split,

    Source2 = YourTable,
          #"Added Custom" = Table.AddColumn(Source2, "SplitPosition", each SplitPos([Original])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column1", each if [SplitPosition]<> null then Text.Range([Original],0,[SplitPosition]) else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Column2", each if [SplitPosition]<> null then Text.Range([Original],[SplitPosition], Text.Length([Original])-[SplitPosition]) else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom2",{"SplitPosition"})
in
    #"Removed Columns1"

      You have to replace "YourTable" in step "Source2" by a reference to your query/table.