Hi guys,
I hope you are well. Just wondering if anyone knows how to put substrings into a specific order?
Lets say I have the following columns, and want the values in the substrings to be ordered alphabetically (or any order I wanted to apply, E.G if the data was roles like President, Vice-President I would want to order by a hierarchy I can define)
| Project |
Roles |
Desired Output |
| 1 |
B, D, C |
B, C, D |
| 2 |
B, A, C |
A, B, C |
| 3 |
A, Z, E |
A, E, Z |
| 4 |
B, X, I |
B, I, X |
I've tried a few things but keep coming up sort. I tried using:
Pick(wildmatch(Roles, '*A*'),'A')& ' , ' &
Pick(wildmatch(Roles, '*B*'),'B')& ' , ' &
Pick(wildmatch(Roles, '*C*'),'C')& ' , ' &
Pick(wildmatch(Roles, '*D*'),'D')& ' , ' &
Pick(wildmatch(Roles, '*E*'),'E')& ' , '
ect.
as Ordered_Strings
However, I end up with stray ' , ' in the new field which I'm not able to get rid of easily as I would like the delimiter between elements that are actually found to still be there. I have also tried using Concat in combination with the function above but keep getting errors.
Any help or guidance would be greatly appreciated.
