Starting from the “civici” column, I would like to obtain only two columns: a column with the first value (before the first comma) and a column with the last value of the number (after the last comma).
How can I do? using cell splitter I have a variable number of columns and I don’t know if I took the right path or not. Thank you
Civici.xlsx (9.1 KB)
Hi @MGDiNicola , this can be performed using the Regex Split rather than the Cell Splitter,
To capture the entire value prior to the first comma, and the entire value after the final comma (and allow for optional spaces), use this:
(.*?),.*,[ ]*(.+?)
i.e.
This captures a number of characters prior to a comma , then ignores all remaining characters by any characters until followed by a comma and zero,one or more spaces, and then captures the remaining characters after that. The captures are “non-greedy” (hence the ?) so that in each case they capture the minimum number of characters that satisfy the whole regex. Thus before FIRST comma, and after LAST comma.
If you wanted to use Cell Splitter, and avoid Regex… you could use Cell Splitter to split to a List instead of columns.
After that, you can use Ungroup.
Then use GroupBy and group by “Civici” and perform aggregations of First and Last on the SplitResult column (ignoring missing):
3 Likes
It seems to work, I just have a problem where I have only one value in the civic column or at most two values. From three values up, it works perfectly
Hi @MGDiNicola , this of course demonstrates the problem with not providing example data that covers any possible edge-cases that don’t conform to the “perceived specification”. Whilst the regex could (possibly?) be adapted to work here, for simplicity, in this case I would go with the second example I gave that goes back to using Cell Splitter along with ungroup and groupby. I believe that works for both those additional scenarios.
2 Likes
