I am trying to split a string (the string length is uncertain; it could be 500 characters or 1500 characters) into multiple columns, and each column should only contain 5 characters.
For example,
If column A contains the string:
AAGANAB5ARAB7AAAB9AAAC--CAC--1ACMRD
Then, I need Column B to Column H to be:
AAGAN
AB5AR
AB7AA
AB9AA
AC--C
AC--1
ACMRD
Also, the string contains “-“, but these are NOT delimiters. They should also be counted as a part of 5 char strings.
I am not sure how to do it, tried to search online. And I found RegEx is probably the function I should use, but not sure how to write the formula in this case.
Solved! Go to Solution.
You could try something like this to parse it out into rows, from there you can use a cross tab tool to get the columns.
Unfortunately, I can't do in this way. I have a lot rows already (like more than 10,000 rows of data), and I don't think this way would work when you have rows after rows.
Thank you!