Split Long Line of Text by every fourth space

[mobermeier]

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

[MarqueeCrew · Accepted solution]

@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 · Accepted solution]

Hi @mobermeier 

You can use the Regex tool with the following expression

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

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 · Accepted solution]

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

[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

[danilang]

@MarqueeCrew 

I totally agree. The 1st thing I did was stick your expression into regex101 to find out what it was doing.  Hopefully I'll remember enough about it to use the technique in the future ;)

Dan