Forum Discussion
Split column by delimiter with a character immediately after it
Hi there,
I have this (dummy) string of text that I am trying to split into columns:
This,piece, of,text,needs,to,be,split
The delimiter I want to split by has a character immediately before and after it.
| This | piece, of | text | needs | to | be | split |
The one that I don't want to split as a delimiter has a space immediately after it.
How do I go about this?
Thanks,
Tom
- Anonymous3 years ago
You can use Replace values, and replace ", " with just " " or something that sticks out like "*', and then split by the comma ",".
--Nate
3 Replies
- AlexisOlson
Super User
A fancier way to do this would be to use a character transition splitter. This will keep the commas though, so you'd probably want to trim those off.
Full sample query you can paste into the Advanced Editor of a new blank query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnILNYpyExNTtVRyE/TKUmtKNHJS01NKdYpyddJStUpLsjJLFGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Split Column by Character Transition" = Table.SplitColumn( Source, "Text", Splitter.SplitTextByCharacterTransition({","}, each _ <> " ") ), #"Trimmed Text" = Table.TransformColumns( #"Split Column by Character Transition", {}, each Text.TrimEnd(_, ",") ) in #"Trimmed Text" - AnonymousNot applicable
You can use Replace values, and replace ", " with just " " or something that sticks out like "*', and then split by the comma ",".
--Nate
- Tomhayw
Helper I
Thank you,
I was too busy trying to overcomplicate it I forgot about the easy way around!