Greetings!
I have a string field that is x characters long. Within the string, there are y groups of 66 character fields. How might I extract y columns containing each of the 66 character groups? There are no patterns in the fields.
Thanks!
Joe
Solved! Go to Solution.
Hi @jdemeyer
Here's one approach using regex tokenize to split the fields into rows every 66 characters. Then you can cross-tab back to columns.
Hi Joe ( @jdemeyer ),
Here are two ways you can achieve this. The first one tokenizes your text field to individual characters, count the number of characters in each field and then creates groups of 5 (in your example that would be 66)
The second methodology uses the substring function of the formula tool.
Again I have created groups of 5 characters, but in your case that should be set to 66.
Hope that helps,
Angelos
@Luke_C I attempted to use Regex as well to define the precise number of characters in each group, but then I noticed that this method omits the characters at the end of your string.
For example your first cell has a length of 445
But after the tile tool, if you count the number of characters and summarize you will get a different figure
396 and 924 can be divided by 66 with zero remainder. My initial thought is that it would output the remaining characters as a different row, although it doesn't 😕 Hate to be this person that points it out, but I was surprised myself that this was the case
Good catch @AngelosPachis! I suppose this would only work if the requirement is exactly 66 with no extra characters.
@Luke_C Yes I guess that's right, but I was wondering if there is a more robust way.
Still you can make it work if you estimate the character count discrepancies and then use a Right function to grab the missing characters from that initial string and union that back to the main stream
@Luke_C & @AngelosPachis - The RegEx method Luke provided will work if we add an additional quantifier to the pattern.
(.{1,66})
This pattern will now look for any single character from 1-66 in length. This will in turn allow for any remaining characters that are less than 66 characters in length to be grouped at the end.
Phil
Thanks @Maskell_Rascal , knew I was missing something.
@jdemeyer Here's an updated version of what I initially posted.
Thanks @Luke_C , @AngelosPachis , and @Maskell_Rascal !
I appreciate the replies and the collaboration! The updated RegEx was the best solution for me. Once I had the strings separated (all of them are exactly 66 characters), I used String operations to extract specific data from the string.
Joe