Forum Discussion

michellepace's avatar
michellepace
Resolver III
5 years ago
Solved

type Text: Clean in-between white space

Hi. Is there a way to parse text to single white spaces only?   That is: "mary    white     helloooo"   Becomes: "mary white helloooo"
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    try this

     

     

    let
    Source = Excel.Workbook(File.Contents("C:yourpath\Q.14 Data.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Single space everything, and trim both ends (don’t make a new column)", type text}}),
    tc=Table.TransformColumns( #"Changed Type", {"Single space everything, and trim both ends (don’t make a new column)", each Text.Combine(List.Select(Text.Split(_," "), each _<>""), " ")})
in
    tc

     

     

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

 

Text.Combine(List.Select(Text.Split("mary    white     helloooo"," "), each _<>""), " ")

 

 

 

I do not intend to boast this solution, but just as a side observation, I point out that this cuts away the blanks that precede the first word or follow the last word.
This of course can be an advantage or a disadvantage, it depends on what one is looking for.