PQ - Text.Format

Text.Format

Phone		Proper
1234567890		(123) 456-7890
3334587091		(333) 458-7091
Raw		FName	LName
JOHNMalkovich		John	Malkovich
MIKAELDoe		Mikael	Doe

1.

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Phone", type text}}),
    TF = Table.AddColumn(Type, "Proper", each Text.Format("(#{0}) #{1}-#{2}", Splitter.SplitTextByLengths({3,3,4}) ([Phone]) )),
    TSC = Table.SelectColumns(TF,{"Proper"})
in
    TSC

---

2.

Code:

---

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Caps = Table.AddColumn(Source, "F", each Text.Select([Raw],{"A".."Z"})),
    Len = Table.TransformColumns(Caps,{{"F", Text.Length, Int64.Type}}),
    F = Table.TransformColumns(Len, {{"F", each _ - 1, type number}}),
    L = Table.AddColumn(F, "L", each Text.Length([Raw])-[F]),
    Comma = Table.AddColumn(L, "Custom.1", each Text.Format("#{0},#{1}", Splitter.SplitTextByLengths({[F],[L]}) ([Raw]) )),
    Proper = Table.TransformColumns(Comma,{{"Custom.1", Text.Proper, type text}}),
    Split = Table.SplitColumn(Proper, "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FName", "LName"}),
    TSC = Table.SelectColumns(Split,{"FName", "LName"})
in
    TSC

---