Forum Discussion
Find a string, add a space in the middle
Try this Custom Column
Please see attached file as well
It works with your sample data :)
=let mylist=Text.ToList([Data]),
mycount=List.Count(mylist),
num={"0".."9"},
alpha={"A".."Z","a".."z"}
in
Text.Combine(List.Generate(()=>[a=0,b=mylist{a}],
each [a]< mycount,each [a=[a]+1,b= if
List.Contains(num,mylist{a})
and
List.Contains(alpha,mylist{a+1}) then mylist{a} & " " else mylist{a}],each [b] ))Hi cfraser,
another solution (2 lines of code) which also works with your sample. It splits the column by the last transition from digit to char and then combine these 2 new columns again together.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYpLDgIhDECv0rB2kkFjjMsKVapMUSjqhHD/a0jU5fu0Zuy0n3azVZRLqFcWj5HhxBKQ34O4aGanoIwyqmNdTd80c7AeKa4CPvOTYDxhSeL/LWDW8krJQ074k+eICkcIVG6kAVyqWWFr50clkgL3nBYS9PSdwfT+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), SplitColumn = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z"}), 2), CombineColumn = Table.CombineColumns(SplitColumn, {"Data.1", "Data.2"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Data")in CombineColumn
v-piga-msft I was hoping to achieve it using Power Query, but I simply don't know what functions to use to achieve it.
I have some additional examples below of actual data.
| Data | Desired output |
| 1-5-301TANGHUJINDALI BINHAIXINDISTRICT TIANJINCITY | 1-5-301 TANGHUJINDALI BINHAIXINDISTRICT TIANJINCITY |
| 71DAELYN DRIVE RICHMOND | 71 DAELYN DRIVE RICHMOND |
| 71HARTSWOOD ROAD | 71 HARTSWOOD ROAD |
| FLAT 9 HESKETH COURT 210QUEENS PROMENADE | FLAT 9 HESKETH COURT 210 QUEENS PROMENADE |
Thanks,
Callum
Try this Custom Column
Please see attached file as well
It works with your sample data :)
=let mylist=Text.ToList([Data]),
mycount=List.Count(mylist),
num={"0".."9"},
alpha={"A".."Z","a".."z"}
in
Text.Combine(List.Generate(()=>[a=0,b=mylist{a}],
each [a]< mycount,each [a=[a]+1,b= if
List.Contains(num,mylist{a})
and
List.Contains(alpha,mylist{a+1}) then mylist{a} & " " else mylist{a}],each [b] ))
Hi cfraser,
another solution (2 lines of code) which also works with your sample. It splits the column by the last transition from digit to char and then combine these 2 new columns again together.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LYpLDgIhDECv0rB2kkFjjMsKVapMUSjqhHD/a0jU5fu0Zuy0n3azVZRLqFcWj5HhxBKQ34O4aGanoIwyqmNdTd80c7AeKa4CPvOTYDxhSeL/LWDW8krJQ074k+eICkcIVG6kAVyqWWFr50clkgL3nBYS9PSdwfT+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), SplitColumn = Table.SplitColumn(#"Changed Type", "Data", Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z"}), 2), CombineColumn = Table.CombineColumns(SplitColumn, {"Data.1", "Data.2"}, Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "Data")in CombineColumn