PQ - Text.Format

[sandy666]

Text.Format


PhoneProper


1234567890(123) 456-7890


3334587091(333) 458-7091





RawFNameLName

JOHNMalkovichJohnMalkovich

MIKAELDoeMikaelDoe


1.

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.

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