I have a long string of values that is merged together. I need to insert a delimiter after every 6th character; there is no other consistency in the values (i.e. case, what the character is). I am trying to use the RegEx tool but can't figure out how to specify to insert the delimeter in this position.
Example:
String looks like this - ab111cde222fgh333i
Needs to come out like this: ab111c;de222f;gh333i so that I can then separate them into individual columns (ab111c would go in one column, de222f would go in a second, etc.).
Thanks in advance!
Solved! Go to Solution.
Hi @ew87
Can you just get to the parsing without using the delimiter?
If you use a recordID tool to add a unique identifier per row, you can Tokenize in the Regex tool into rows using the following: (.{6}) That grabs six characters at a time.
A Tile tool can give the column number for rebuilding the table, and the Crosstab tool builds the table back for you.
I like using this approach, tokenizing to rows, because your strings could be longer than 3 columns worth of data.
Let me know if that helps.
Cheers!
Esther
Hi @ew87
You can do it all in the one RegEx tool, as attached.
Summarise tool also shown if you wanted to bring it back together as per your output example.
Thanks
Andy
That helped - thank you! I wasn't able to use the unique values + cross tab since there are many other columns I'm working with outside of this one, but using Tokenize in the RegEx tool worked and I was able to output into columns all in one step as you suggested. Thanks!
Thanks for the help. I liked using the ... in Tokenize as it's easier for me to remember going forward. I'll need to play with the summarizing since there are many other columns in my actual file outside of this one, but for now my immediate issue was solved! Thanks!