Forum Discussion
AlB
Community Champion
Community ChampionM: Split column with multiple spaces between fields
Hi all, Imagine we have a column in which we have different fields separated by an arbitrary number of spaces. Example: Column "1 Red 23 Yellow" and we want to...
One way to do this is to split the text into a list based on the delimiter, remove nulls, and then recombine the list into a string with just a single space between words.
= Table.TransformColumns(Source, {{"Column1", each Text.Combine(List.Select(Text.SplitAny(_, " "), each _ <> "")," "), type text}})or in expanded format
= Table.TransformColumns(
Source,
{{"Column1",
each Text.Combine(
List.Select(
Text.SplitAny(_, " "),
each _ <> ""
)
," "
),
type text
}}
)Then you can split this transformed column by the space delimiter.
AlexisOlson
Super User
Super UserOne way to do this is to split the text into a list based on the delimiter, remove nulls, and then recombine the list into a string with just a single space between words.
= Table.TransformColumns(Source, {{"Column1", each Text.Combine(List.Select(Text.SplitAny(_, " "), each _ <> "")," "), type text}})
or in expanded format
= Table.TransformColumns(
Source,
{{"Column1",
each Text.Combine(
List.Select(
Text.SplitAny(_, " "),
each _ <> ""
)
," "
),
type text
}}
)
Then you can split this transformed column by the space delimiter.
JoeRobert06
Helper II
Helper IIthis solution worked great for my same issue. Thanks for contributing!