Query - Split at capital letter in word

[SAM190370]

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 should be in the end.

I would like to use a query to do this.

Any suggestions?

Original	Column 1	Column 2
Morten Juhl AndersenDitte Andersen	Morten Juhl Andersen	Ditte Andersen
Philip Tu DuongMarie Dehn	Philip Tu Duong	Marie Dehn
Marlene Dehn
Line S. Andersen
Christian Koch
Daniel Jaro Damm
Nicklas Jensen
Simon Dyring Larsen
Marlene DehnMarie Dehn		Marie Dehn

[v-huizhn-msft · Accepted solution]

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 · Accepted solution]

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]

Hi @v-huizhn-msft,

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

Thx.

/ Søren (Denmark)

[ImkeF]

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.

[SAM190370]

Hi @ImkeF,

I agree that this solution will be more suitable.  However, I gave it a try but couldn't make it work.  I have attached an example of the "Original" file.  Maybe it works for you?

https://drive.google.com/file/d/0Bw9CCgvO54Y9Q0VYRkc4QVNVRjQ/view?usp=sharing

/ Søren

[ImkeF · Accepted solution]

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

[SAM190370]

HI @ImkeF,

You understanding was correct and I get it now. It was my understanding of your solution that whas wrong:-)  I understood source as "file" and not "table/query".................

Thx.

/ Søren

[Greg_Deckler]

That strikes me as a problem that would require regular expressions. You might be able to use R to get you there.