# Power Query - split to columns by capital letter



## pepe74287 (Apr 25, 2016)

Hello,

Another question related to Column split. This time I would need to split data into 2 columns, where delimiter is last capital letter.

For instance:

Robert DuvallCrime
TabuDrama

Should be splitted as:


Column1Column2Robert DuvallCrimeTabuDrama

Thanks
Pepe


----------



## billszysz (Apr 25, 2016)

Try this code below

```
let
    source = Table.FromColumns({ {"Robert DuvallCrime", "TabuDrama"} }, {"text"}),
    AddCol = Table.AddColumn(source, "Custom", each Record.FromList(Splitter.SplitTextByPositions({0,Text.PositionOfAny([text], {"A".."Z"}, 1)}) ([text]), {"Column1", "Column2"})),
    ExpandRecordColumn = Table.ExpandRecordColumn(AddCol, "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
    RemoveCol = Table.RemoveColumns(ExpandRecordColumn,{"text"})
in
    RemoveCol
```

Regards


----------



## pepe74287 (Apr 25, 2016)

Bill,

ExcelIsFun is right, you truly are Power Query Wizard 

Thanks again, Pepe


----------

