Split Long Line of Text by every fourth space

Data:

0 1155 0 BR 43 3913 51 1170L 1 1706 0 15L 44 3948 53 1190L 2 2026 0 185L 45 4000 59 1215L

Hi, I want to parse this long line of text into one column with multiple rows separated by every fourth space- each row containing four pieces of data, then the next four then the next four. For instance, the first row would contain "0 1155 0 BR", the second row "43 3913 51 1170L", etc until the end.

Please advise and many many thanks!

Mary

Parse

Accepted answers

MarqueeCrew

@mobermeier,

This is a Monday question for sure. Yikes!

((?:\S+\s){3}\S+)\s

Now if you use a RegEx tool, you can place that negative look-ahead into the expression, make the output method "tokenize" and use the "split to rows" radio button.

Mary, google helped me to find an expression for you.

Cheers,

Mark

danilang

Hi @mobermeier

You can use the Regex tool with the following expression

\w*\s\w*\s\w*\s\w*\s

Split 4.png

Edit: Look for \w*(any number of word characters) followed \s(space) repeated 4 times. Less sophisticated than @MarqueeCrew, but easier for a Regex newbie like myself to understand!

Dan

CharlieS

There's a few ways to do this, but here's how I went about it:

- Split to rows on every space

- Use a RecordID and a CEIL( formula to create groups of 4 records

- Summarize on the new group record and concatenate with spaces

20190304-Groupsof4.yxmd

All comments

MarqueeCrew

@mobermeier,

This is a Monday question for sure. Yikes!

((?:\S+\s){3}\S+)\s

Now if you use a RegEx tool, you can place that negative look-ahead into the expression, make the output method "tokenize" and use the "split to rows" radio button.

Mary, google helped me to find an expression for you.

Cheers,

Mark

danilang

Hi @mobermeier

You can use the Regex tool with the following expression

\w*\s\w*\s\w*\s\w*\s

Split 4.png

Edit: Look for \w*(any number of word characters) followed \s(space) repeated 4 times. Less sophisticated than @MarqueeCrew, but easier for a Regex newbie like myself to understand!

Dan

CharlieS

There's a few ways to do this, but here's how I went about it:

- Split to rows on every space

- Use a RecordID and a CEIL( formula to create groups of 4 records

- Summarize on the new group record and concatenate with spaces

20190304-Groupsof4.yxmd

mobermeier

Thank you this worked! I really appreciate your time!

mobermeier

Thank you, this worked! I really appreciate your time!

MarqueeCrew

@danilang,

Any approach that satisfies the user's challenge is a good solution in my book. I hope that we all get accepted solutions on this post. Besides being "sophisticated", my approach benefits from not bringing that extra space forward into the output. It also demonstrates the potential for more complex lookups.

Cheers,

Mark

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703