Hello everyone,
I'm having some strings as follow:
, 5536463784, 5536643484
, , 5536643383, 5536644436, 5536644990
, , , 5536630664, 5536630770, 5536633864, 5536641613
, , , , , , , , , , 5536634549, 5536635255, 5536636403, 5536636437, 5536636540, 5536636541, 5536636718, 5536637694, 5536637695, 5536642011, 5536642012
These strings are results of concatenating using ", " as separator. I would like to ask whether we can use RegEx or any other idea to remove the unwanted delimiters, so the output can be like this:
5536463784, 5536643484
5536643383, 5536644436, 5536644990
5536630664, 5536630770, 5536633864, 5536641613
5536634549, 5536635255, 5536636403, 5536636437, 5536636540, 5536636541, 5536636718, 5536637694, 5536637695, 5536642011, 5536642012
Thank you very much
Trinh
Solved! Go to Solution.
Hi @daophuongtrinh you can use Regex to solve this.
REGEX_Replace([Field1], "^\,*(\d+.*)", "$1")
This will look for a comma or commas e.g. ^\,* at the begining then it will find a digit or more aka eveything else and it will replace it with that last part.
Thank you very much for your suggestion. However the beginning delimiter is comma with space, and I have tried to add "\s" to the expression but somehow it didn't work.
Could you help take a look at the workflow I attached?
Hi @daophuongtrinh amened the regex to work in this scenrio and also found a non-regex way to solve this as well.
@daophuongtrinh
I think we can not restrict the order of comma and space.
REGEX_Replace([Field1], "^[\,\s]*(\d+.*)", "$1")
one non-regex way is to use the Text to Column tool. The way to visualize the solution is that you currently have concatenated strings which also include “blank” values which are concatenated. So you want to “decompose” them, take out the blank values, and then only concatenate the remaining non-blank values.
this can be done in 4 steps:
1) use a RecordID tool to enable you to recombine the parts without mixing up the original strings
2) use a TextToColumn tool, but choose out put to rows and with comma as the delimiter. After this step you will have many rows than before, each row contains either a blank or a desired string.
3) filter tool: to filter out the blank. Note: do experiment with isempty() and isnull() formula to see how their behaviours differ.
4) use the summarize tool to concatenate the “broken up” parts. Do remember to group by the RecordID field so that you get back the original string combinations minus the blanks.
i did not attach a workflow, hopefully the above schematics gives you enough pointers to reach the desired outcomes.
cheers
dawn
Hi @DawnDuong
Thank you for your idea, however the example I have here is only a column in a dataset and filtering out the blank rows would make the other numeric columns to be incorrect so I think we cannot go with this option though.
You need do the first 2 steps :)
do explore the uniqueID tool and the TextToColumn Tool - these are really handy
dawn
@daophuongtrinh
Glad to be any help. 😁